1 --Upgrade Script for 3.7.1 to 3.8-beta
2 \set eg_version '''3.8-beta'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.8-beta', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1260', :eg_version);
8 INSERT into config.org_unit_setting_type
9 (name, grp, label, description, datatype)
11 'ui.patron.edit.au.photo_url.require',
14 'ui.patron.edit.au.photo_url.require',
15 'Require Photo URL field on patron registration',
20 'ui.patron.edit.au.photo_url.require',
21 'The Photo URL field will be required on the patron registration screen.',
28 INSERT into config.org_unit_setting_type
29 (name, grp, label, description, datatype)
31 'ui.patron.edit.au.photo_url.show',
34 'ui.patron.edit.au.photo_url.show',
35 'Show Photo URL field on patron registration',
40 'ui.patron.edit.au.photo_url.show',
41 'The Photo URL field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.',
48 INSERT into config.org_unit_setting_type
49 (name, grp, label, description, datatype)
51 'ui.patron.edit.au.photo_url.suggest',
54 'ui.patron.edit.au.photo_url.suggest',
55 'Suggest Photo URL field on patron registration',
61 'ui.patron.edit.au.photo_url.suggest',
62 'The Photo URL field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.',
69 INSERT INTO permission.perm_list ( id, code, description ) VALUES
70 ( 632, 'UPDATE_USER_PHOTO_URL', oils_i18n_gettext( 632,
71 'Update the user photo url field in patron registration and editor', 'ppl', 'description' ))
74 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
76 pgt.id, perm.id, aout.depth, FALSE
78 permission.grp_tree pgt,
79 permission.perm_list perm,
80 actor.org_unit_type aout
82 pgt.name = 'Circulators' AND
83 aout.name = 'System' AND
84 perm.code = 'UPDATE_USER_PHOTO_URL'
88 SELECT evergreen.upgrade_deps_block_check('1266', :eg_version);
90 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
92 'eg.grid.catalog.record.copies', 'gui', 'object',
94 'eg.grid.catalog.record.copies',
95 'Grid Config: eg.grid.catalog.record.copies',
100 SELECT evergreen.upgrade_deps_block_check('1267', :eg_version);
102 SELECT auditor.create_auditor ( 'acq', 'fund_debit' );
106 SELECT evergreen.upgrade_deps_block_check('1268', :eg_version);
108 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
110 'eg.staff.catalog.results.show_more', 'gui', 'bool',
112 'eg.staff.catalog.results.show_more',
113 'Show more details in Angular staff catalog',
119 SELECT evergreen.upgrade_deps_block_check('1269', :eg_version);
124 WHERE code IN ('VIEW_BOOKING_RESERVATION', 'VIEW_BOOKING_RESERVATION_ATTR_MAP'))
126 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
127 SELECT grp, perms_to_add.id as perm, depth, grantable
129 permission.grp_perm_map
131 --- Don't add the permissions if they have already been assigned
133 (SELECT DISTINCT grp FROM permission.grp_perm_map
134 INNER JOIN perms_to_add ON perm=perms_to_add.id)
136 --- Anybody who can view resources should also see reservations
137 --- at the same level
140 FROM permission.perm_list
141 WHERE code = 'VIEW_BOOKING_RESOURCE'
146 SELECT evergreen.upgrade_deps_block_check('1270', :eg_version);
148 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Srce', '008', 'BKS', 39, 1, ' ');
149 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Srce', '008', 'COM', 39, 1, ' ');
150 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Srce', '008', 'MAP', 39, 1, ' ');
151 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Srce', '008', 'MIX', 39, 1, ' ');
152 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Srce', '008', 'REC', 39, 1, ' ');
153 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Srce', '008', 'SCO', 39, 1, ' ');
154 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Srce', '008', 'SER', 39, 1, ' ');
155 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Srce', '008', 'VIS', 39, 1, ' ');
158 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('srce','Srce','Srce');
160 INSERT INTO config.coded_value_map (id, ctype, code, value) VALUES
161 (1750, 'srce', ' ', oils_i18n_gettext('1750', 'National bibliographic agency', 'ccvm', 'value')),
162 (1751, 'srce', 'c', oils_i18n_gettext('1751', 'Cooperative cataloging program', 'ccvm', 'value')),
163 (1752, 'srce', 'd', oils_i18n_gettext('1752', 'Other', 'ccvm', 'value'));
166 SELECT evergreen.upgrade_deps_block_check('1271', :eg_version);
168 INSERT INTO config.org_unit_setting_type
169 (grp, name, datatype, label, description, update_perm, view_perm)
172 'credit.processor.stripe.currency', 'string',
174 'credit.processor.stripe.currency',
175 'Stripe ISO 4217 currency code',
180 'credit.processor.stripe.currency',
181 'Use an all lowercase version of a Stripe-supported ISO 4217 currency code. Defaults to "usd"',
185 (SELECT id FROM permission.perm_list WHERE code = 'ADMIN_CREDIT_CARD_PROCESSING'),
186 (SELECT id FROM permission.perm_list WHERE code = 'VIEW_CREDIT_CARD_PROCESSING')
190 SELECT evergreen.upgrade_deps_block_check('1272', :eg_version);
195 PERFORM FROM config.usr_setting_type WHERE name = 'circ.collections.exempt';
199 INSERT INTO config.usr_setting_type (
207 'circ.collections.exempt',
210 'circ.collections.exempt',
211 'Collections: Exempt',
216 'circ.collections.exempt',
217 'User is exempt from collections tracking/processing',
231 SELECT evergreen.upgrade_deps_block_check('1273', :eg_version);
233 INSERT into config.org_unit_setting_type
234 ( name, grp, label, description, datatype )
235 SELECT 'opac.did_you_mean.max_suggestions',
237 'Maximum number of spelling suggestions that may be offered',
238 'If set to -1, provide "best" suggestion if mispelled; if set higher than 0, the maximum suggestions that can be provided; if set to 0, disable suggestions.',
240 WHERE NOT EXISTS (SELECT 1 FROM config.org_unit_setting_type WHERE name = 'opac.did_you_mean.max_suggestions');
244 SELECT evergreen.upgrade_deps_block_check('1274', :eg_version);
246 CREATE INDEX poi_fund_debit_idx ON acq.po_item (fund_debit);
247 CREATE INDEX ii_fund_debit_idx ON acq.invoice_item (fund_debit);
250 SELECT evergreen.upgrade_deps_block_check('1275', :eg_version);
252 CREATE OR REPLACE FUNCTION acq.transfer_fund(
254 old_amount IN NUMERIC, -- in currency of old fund
256 new_amount IN NUMERIC, -- in currency of new fund
258 xfer_note IN TEXT -- to be recorded in acq.fund_transfer
259 -- ,funding_source_in IN INT -- if user wants to specify a funding source (see notes)
261 /* -------------------------------------------------------------------------------
263 Function to transfer money from one fund to another.
265 A transfer is represented as a pair of entries in acq.fund_allocation, with a
266 negative amount for the old (losing) fund and a positive amount for the new
267 (gaining) fund. In some cases there may be more than one such pair of entries
268 in order to pull the money from different funding sources, or more specifically
269 from different funding source credits. For each such pair there is also an
270 entry in acq.fund_transfer.
272 Since funding_source is a non-nullable column in acq.fund_allocation, we must
273 choose a funding source for the transferred money to come from. This choice
274 must meet two constraints, so far as possible:
276 1. The amount transferred from a given funding source must not exceed the
277 amount allocated to the old fund by the funding source. To that end we
278 compare the amount being transferred to the amount allocated.
280 2. We shouldn't transfer money that has already been spent or encumbered, as
281 defined by the funding attribution process. We attribute expenses to the
282 oldest funding source credits first. In order to avoid transferring that
283 attributed money, we reverse the priority, transferring from the newest funding
284 source credits first. There can be no guarantee that this approach will
285 avoid overcommitting a fund, but no other approach can do any better.
287 In this context the age of a funding source credit is defined by the
288 deadline_date for credits with deadline_dates, and by the effective_date for
289 credits without deadline_dates, with the proviso that credits with deadline_dates
290 are all considered "older" than those without.
294 In the signature for this function, there is one last parameter commented out,
295 named "funding_source_in". Correspondingly, the WHERE clause for the query
296 driving the main loop has an OR clause commented out, which references the
297 funding_source_in parameter.
299 If these lines are uncommented, this function will allow the user optionally to
300 restrict a fund transfer to a specified funding source. If the source
301 parameter is left NULL, then there will be no such restriction.
303 ------------------------------------------------------------------------------- */
305 same_currency BOOLEAN;
306 currency_ratio NUMERIC;
307 old_fund_currency TEXT;
308 old_remaining NUMERIC; -- in currency of old fund
309 new_fund_currency TEXT;
310 new_fund_active BOOLEAN;
311 new_remaining NUMERIC; -- in currency of new fund
312 curr_old_amt NUMERIC; -- in currency of old fund
313 curr_new_amt NUMERIC; -- in currency of new fund
314 source_addition NUMERIC; -- in currency of funding source
315 source_deduction NUMERIC; -- in currency of funding source
316 orig_allocated_amt NUMERIC; -- in currency of funding source
317 allocated_amt NUMERIC; -- in currency of fund
319 old_fund_row acq.fund%ROWTYPE;
320 new_fund_row acq.fund%ROWTYPE;
321 old_org_row actor.org_unit%ROWTYPE;
322 new_org_row actor.org_unit%ROWTYPE;
327 IF old_fund IS NULL THEN
328 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
331 IF old_amount IS NULL THEN
332 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
335 -- The new fund and its amount must be both NULL or both not NULL.
337 IF new_fund IS NOT NULL AND new_amount IS NULL THEN
338 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
341 IF new_fund IS NULL AND new_amount IS NOT NULL THEN
342 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
345 IF user_id IS NULL THEN
346 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
349 -- Initialize the amounts to be transferred, each denominated
350 -- in the currency of its respective fund. They will be
351 -- reduced on each iteration of the loop.
353 old_remaining := old_amount;
354 new_remaining := new_amount;
356 -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
357 -- old_amount, old_fund, new_amount, new_fund;
359 -- Get the currency types of the old and new funds.
370 IF old_fund_currency IS NULL THEN
371 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
374 IF new_fund IS NOT NULL THEN
386 IF new_fund_currency IS NULL THEN
387 RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
388 ELSIF NOT new_fund_active THEN
390 -- No point in putting money into a fund from whence you can't spend it
392 RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
395 IF new_amount = old_amount THEN
396 same_currency := true;
400 -- We'll have to translate currency between funds. We presume that
401 -- the calling code has already applied an appropriate exchange rate,
402 -- so we'll apply the same conversion to each sub-transfer.
404 same_currency := false;
405 currency_ratio := new_amount / old_amount;
409 -- Fetch old and new fund's information
410 -- in order to construct the allocation notes
411 SELECT INTO old_fund_row * FROM acq.fund WHERE id = old_fund;
412 SELECT INTO old_org_row * FROM actor.org_unit WHERE id = old_fund_row.org;
413 SELECT INTO new_fund_row * FROM acq.fund WHERE id = new_fund;
414 SELECT INTO new_org_row * FROM actor.org_unit WHERE id = new_fund_row.org;
417 -- Identify the funding source(s) from which we want to transfer the money.
418 -- The principle is that we want to transfer the newest money first, because
419 -- we spend the oldest money first. The priority for spending is defined
420 -- by a sort of the view acq.ordered_funding_source_credit.
427 ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
431 acq.ordered_funding_source_credit AS ofsc,
432 acq.funding_source fs
434 ofsc.funding_source = fs.id
435 and ofsc.funding_source IN
437 SELECT funding_source
438 FROM acq.fund_allocation
439 WHERE fund = old_fund
443 -- ofsc.funding_source = funding_source_in
444 -- OR funding_source_in IS NULL
447 ofsc.sort_priority desc,
452 -- Determine how much money the old fund got from this funding source,
453 -- denominated in the currency types of the source and of the fund.
454 -- This result may reflect transfers from previous iterations.
457 COALESCE( sum( amount ), 0 ),
458 COALESCE( sum( amount )
459 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
461 orig_allocated_amt, -- in currency of the source
462 allocated_amt -- in currency of the old fund
467 and funding_source = source.funding_source;
469 -- Determine how much to transfer from this credit, in the currency
470 -- of the fund. Begin with the amount remaining to be attributed:
472 curr_old_amt := old_remaining;
474 -- Can't attribute more than was allocated from the fund:
476 IF curr_old_amt > allocated_amt THEN
477 curr_old_amt := allocated_amt;
480 -- Can't attribute more than the amount of the current credit:
482 IF curr_old_amt > source.converted_amt THEN
483 curr_old_amt := source.converted_amt;
486 curr_old_amt := trunc( curr_old_amt, 2 );
488 old_remaining := old_remaining - curr_old_amt;
490 -- Determine the amount to be deducted, if any,
491 -- from the old allocation.
493 IF old_remaining > 0 THEN
495 -- In this case we're using the whole allocation, so use that
496 -- amount directly instead of applying a currency translation
497 -- and thereby inviting round-off errors.
499 source_deduction := - curr_old_amt;
501 source_deduction := trunc(
503 acq.exchange_ratio( old_fund_currency, source.currency_type ),
507 IF source_deduction <> 0 THEN
509 -- Insert negative allocation for old fund in fund_allocation,
510 -- converted into the currency of the funding source
512 INSERT INTO acq.fund_allocation (
519 source.funding_source,
523 'Transfer to fund ' || new_fund_row.code || ' ('
524 || new_fund_row.year || ') ('
525 || new_org_row.shortname || ')'
529 IF new_fund IS NOT NULL THEN
531 -- Determine how much to add to the new fund, in
532 -- its currency, and how much remains to be added:
534 IF same_currency THEN
535 curr_new_amt := curr_old_amt;
537 IF old_remaining = 0 THEN
539 -- This is the last iteration, so nothing should be left
541 curr_new_amt := new_remaining;
544 curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
545 new_remaining := new_remaining - curr_new_amt;
549 -- Determine how much to add, if any,
550 -- to the new fund's allocation.
552 IF old_remaining > 0 THEN
554 -- In this case we're using the whole allocation, so use that amount
555 -- amount directly instead of applying a currency translation and
556 -- thereby inviting round-off errors.
558 source_addition := curr_new_amt;
559 ELSIF source.currency_type = old_fund_currency THEN
561 -- In this case we don't need a round trip currency translation,
562 -- thereby inviting round-off errors:
564 source_addition := curr_old_amt;
566 source_addition := trunc(
568 acq.exchange_ratio( new_fund_currency, source.currency_type ),
572 IF source_addition <> 0 THEN
574 -- Insert positive allocation for new fund in fund_allocation,
575 -- converted to the currency of the founding source
577 INSERT INTO acq.fund_allocation (
584 source.funding_source,
588 'Transfer from fund ' || old_fund_row.code || ' ('
589 || old_fund_row.year || ') ('
590 || old_org_row.shortname || ')'
595 IF trunc( curr_old_amt, 2 ) <> 0
596 OR trunc( curr_new_amt, 2 ) <> 0 THEN
598 -- Insert row in fund_transfer, using amounts in the currency of the funds
600 INSERT INTO acq.fund_transfer (
607 funding_source_credit
610 trunc( curr_old_amt, 2 ),
612 trunc( curr_new_amt, 2 ),
619 if old_remaining <= 0 THEN
620 EXIT; -- Nothing more to be transferred
627 SELECT evergreen.upgrade_deps_block_check('1276', :eg_version);
629 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
631 'eg.grid.acq.fund.fund_debit', 'gui', 'object',
633 'eg.grid.acq.fund.fund_debit',
634 'Grid Config: eg.grid.acq.fund.fund_debit',
638 'eg.grid.acq.fund.fund_transfer', 'gui', 'object',
640 'eg.grid.acq.fund.fund_transfer',
641 'Grid Config: eg.grid.acq.fund.fund_transfer',
645 'eg.grid.acq.fund.fund_allocation', 'gui', 'object',
647 'eg.grid.acq.fund.fund_allocation',
648 'Grid Config: eg.grid.acq.fund.fund_allocation',
652 'eg.grid.admin.acq.fund', 'gui', 'object',
654 'eg.grid.admin.acq.fund',
655 'Grid Config: eg.grid.admin.acq.fund',
659 'eg.grid.admin.acq.funding_source', 'gui', 'object',
661 'eg.grid.admin.acq.funding_source',
662 'Grid Config: eg.grid.admin.acq.funding_source',
666 'eg.grid.acq.funding_source.fund_allocation', 'gui', 'object',
668 'eg.grid.acq.funding_source.fund_allocation',
669 'Grid Config: eg.grid.acq.funding_source.fund_allocation',
673 'eg.grid.acq.funding_source.credit', 'gui', 'object',
675 'eg.grid.acq.funding_source.credit',
676 'Grid Config: eg.grid.acq.funding_source.credit',
682 SELECT evergreen.upgrade_deps_block_check('1277', :eg_version);
684 -- if there are any straggling funds without a code set, fix that
686 SET code = 'FUND-WITH-ID-' || id
690 ALTER COLUMN code SET NOT NULL;
693 SELECT evergreen.upgrade_deps_block_check('1278', :eg_version);
695 CREATE OR REPLACE VIEW reporter.asset_call_number_dewey AS
696 SELECT id AS call_number,
697 call_number_dewey(label) AS dewey,
698 CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text
699 THEN btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text))
701 END AS dewey_block_tens,
702 CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text
703 THEN btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text))
705 END AS dewey_block_hundreds,
706 CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text
707 THEN (btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text)) || '-'::text)
708 || btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision) + 9::double precision, '000'::text))
710 END AS dewey_range_tens,
711 CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text
712 THEN (btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text)) || '-'::text)
713 || btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision) + 99::double precision, '000'::text))
715 END AS dewey_range_hundreds
716 FROM asset.call_number
717 WHERE call_number_dewey(label) ~ '^[0-9]'::text;
721 SELECT evergreen.upgrade_deps_block_check('1279', :eg_version);
723 UPDATE config.org_unit_setting_type SET fm_class='cnal', datatype='link' WHERE name='ui.patron.default_inet_access_level';
727 SELECT evergreen.upgrade_deps_block_check('1280', :eg_version);
729 UPDATE config.org_unit_setting_type
730 SET description = $$How long to wait before allowing opportunistic capture of holds with a pickup library other than the context item's circulating library$$ -- ' vim
731 WHERE name = 'circ.hold_stalling.soft';
733 INSERT into config.org_unit_setting_type
734 ( name, grp, label, description, datatype, fm_class ) VALUES
735 ( 'circ.pickup_hold_stalling.soft',
737 'Pickup Library Soft stalling interval',
738 'When set for the pickup library, this specifies that for holds with a request time age smaller than this interval only items scanned at the pickup library can be opportunistically captured. Example "5 days". This setting takes precedence over "Soft stalling interval" (circ.hold_stalling.soft) when the interval is in force.',
743 INSERT into config.org_unit_setting_type
744 ( name, grp, label, description, datatype, fm_class ) VALUES
745 ( 'circ.pickup_hold_stalling.hard',
747 'Pickup Library Hard stalling interval',
748 'When set for the pickup library, this specifies that no items with a calculated proximity greater than 0 from the pickup library can be directly targeted for this time period if there are local available copies. Example "3 days".',
755 SELECT evergreen.upgrade_deps_block_check('1281', :eg_version);
757 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
759 'eg.cat.volcopy.defaults', 'cat', 'object',
761 'eg.cat.volcopy.defaults',
762 'Holdings Editor Default Values and Visibility',
768 SELECT evergreen.upgrade_deps_block_check('1282', :eg_version);
770 CREATE OR REPLACE FUNCTION search.symspell_lookup(
773 verbosity integer DEFAULT 2,
774 xfer_case boolean DEFAULT false,
775 count_threshold integer DEFAULT 1,
776 soundex_weight integer DEFAULT 0,
777 pg_trgm_weight integer DEFAULT 0,
778 kbdist_weight integer DEFAULT 0
779 ) RETURNS SETOF search.symspell_lookup_output
787 edit_list TEXT[] := '{}';
788 seen_list TEXT[] := '{}';
789 output search.symspell_lookup_output;
790 output_list search.symspell_lookup_output[];
798 smallest_ed INT := -1;
803 SELECT value::INT INTO prefix_length FROM config.internal_flag WHERE name = 'symspell.prefix_length' AND enabled;
804 prefix_length := COALESCE(prefix_length, 6);
806 SELECT value::INT INTO maxED FROM config.internal_flag WHERE name = 'symspell.max_edit_distance' AND enabled;
807 maxED := COALESCE(maxED, 3);
809 word_list := ARRAY_AGG(x) FROM search.symspell_parse_words(raw_input) x;
811 -- Common case exact match test for preformance
812 IF verbosity = 0 AND CARDINALITY(word_list) = 1 AND CHARACTER_LENGTH(word_list[1]) <= prefix_length THEN
814 'SELECT '||search_class||'_suggestions AS suggestions,
815 '||search_class||'_count AS count,
817 FROM search.symspell_dictionary
818 WHERE prefix_key = $1
819 AND '||search_class||'_count >= $2
820 AND '||search_class||'_suggestions @> ARRAY[$1]'
821 INTO entry USING evergreen.lowercase(word_list[1]), COALESCE(count_threshold,1);
822 IF entry.prefix_key IS NOT NULL THEN
823 output.lev_distance := 0; -- definitionally
824 output.prefix_key := entry.prefix_key;
825 output.prefix_key_count := entry.count;
826 output.suggestion_count := entry.count;
827 output.input := word_list[1];
829 output.suggestion := search.symspell_transfer_casing(output.input, entry.prefix_key);
831 output.suggestion := entry.prefix_key;
833 output.norm_input := entry.prefix_key;
834 output.qwerty_kb_match := 1;
835 output.pg_trgm_sim := 1;
836 output.soundex_sim := 1;
843 FOREACH word IN ARRAY word_list LOOP
845 input := evergreen.lowercase(word);
846 i_len := CHARACTER_LENGTH(input);
849 IF CHARACTER_LENGTH(input) > prefix_length THEN
850 prefix_key := SUBSTRING(input FROM 1 FOR prefix_length);
851 edit_list := ARRAY[input,prefix_key] || search.symspell_generate_edits(prefix_key, 1, l_maxED);
853 edit_list := input || search.symspell_generate_edits(input, 1, l_maxED);
856 SELECT ARRAY_AGG(x ORDER BY CHARACTER_LENGTH(x) DESC) INTO edit_list FROM UNNEST(edit_list) x;
863 FOREACH entry_key IN ARRAY edit_list LOOP
865 IF global_ed IS NOT NULL THEN
866 smallest_ed := global_ed;
870 'SELECT '||search_class||'_suggestions AS suggestions,
871 '||search_class||'_count AS count,
873 FROM search.symspell_dictionary
874 WHERE prefix_key = $1
875 AND '||search_class||'_suggestions IS NOT NULL'
881 ARRAY[s, evergreen.levenshtein_damerau_edistance(input,s,l_maxED)::TEXT]
882 ORDER BY evergreen.levenshtein_damerau_edistance(input,s,l_maxED) DESC
886 FROM UNNEST(entry.suggestions) s
887 WHERE (ABS(CHARACTER_LENGTH(s) - i_len) <= maxEd AND evergreen.levenshtein_damerau_edistance(input,s,l_maxED) BETWEEN 0 AND l_maxED)
888 AND NOT seen_list @> ARRAY[s];
890 CONTINUE WHEN good_suggs IS NULL;
892 FOR sugg, output.suggestion_count IN EXECUTE
893 'SELECT prefix_key, '||search_class||'_count
894 FROM search.symspell_dictionary
895 WHERE prefix_key = ANY ($1)
896 AND '||search_class||'_count >= $2'
897 USING AKEYS(good_suggs), COALESCE(count_threshold,1)
900 output.lev_distance := good_suggs->sugg;
901 seen_list := seen_list || sugg;
903 -- Track the smallest edit distance among suggestions from this prefix key.
904 IF smallest_ed = -1 OR output.lev_distance < smallest_ed THEN
905 smallest_ed := output.lev_distance;
908 -- Track the smallest edit distance for all prefix keys for this word.
909 IF global_ed IS NULL OR smallest_ed < global_ed THEN
910 global_ed = smallest_ed;
911 -- And if low verbosity, ignore suggs with a larger distance from here on.
912 IF verbosity <= 1 THEN
913 l_maxED := global_ed;
917 -- Lev distance is our main similarity measure. While
918 -- trgm or soundex similarity could be the main filter,
919 -- Lev is both language agnostic and faster.
921 -- Here we will skip suggestions that have a longer edit distance
922 -- than the shortest we've already found. This is simply an
923 -- optimization that allows us to avoid further processing
924 -- of this entry. It would be filtered out later.
925 CONTINUE WHEN output.lev_distance > global_ed AND verbosity <= 1;
927 -- If we have an exact match on the suggestion key we can also avoid
928 -- some function calls.
929 IF output.lev_distance = 0 THEN
930 output.qwerty_kb_match := 1;
931 output.pg_trgm_sim := 1;
932 output.soundex_sim := 1;
934 IF kbdist_weight THEN
935 output.qwerty_kb_match := evergreen.qwerty_keyboard_distance_match(input, sugg);
937 output.qwerty_kb_match := 0;
939 IF pg_trgm_weight THEN
940 output.pg_trgm_sim := similarity(input, sugg);
942 output.pg_trgm_sim := 0;
944 IF soundex_weight THEN
945 output.soundex_sim := difference(input, sugg) / 4.0;
947 output.soundex_sim := 0;
951 -- Fill in some fields
952 IF xfer_case AND input <> word THEN
953 output.suggestion := search.symspell_transfer_casing(word, sugg);
955 output.suggestion := sugg;
957 output.prefix_key := entry.prefix_key;
958 output.prefix_key_count := entry.count;
959 output.input := word;
960 output.norm_input := input;
961 output.word_pos := w_pos;
963 -- We can't "cache" a set of generated records directly, so
964 -- here we build up an array of search.symspell_lookup_output
965 -- records that we can revivicate later as a table using UNNEST().
966 output_list := output_list || output;
968 EXIT entry_key_loop WHEN smallest_ed = 0 AND verbosity = 0; -- exact match early exit
969 CONTINUE entry_key_loop WHEN smallest_ed = 0 AND verbosity = 1; -- exact match early jump to the next key
971 END LOOP; -- loop over suggestions
972 END LOOP; -- loop over entries
973 END LOOP; -- loop over entry_keys
975 -- Now we're done examining this word
976 IF verbosity = 0 THEN
977 -- Return the "best" suggestion from the smallest edit
978 -- distance group. We define best based on the weighting
979 -- of the non-lev similarity measures and use the suggestion
980 -- use count to break ties.
982 SELECT * FROM UNNEST(output_list)
983 ORDER BY lev_distance,
984 (soundex_sim * COALESCE(soundex_weight,0))
985 + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
986 + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
987 suggestion_count DESC
989 ELSIF verbosity = 1 THEN
990 -- Return all suggestions from the smallest
991 -- edit distance group.
993 SELECT * FROM UNNEST(output_list) WHERE lev_distance = smallest_ed
994 ORDER BY (soundex_sim * COALESCE(soundex_weight,0))
995 + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
996 + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
997 suggestion_count DESC;
998 ELSIF verbosity = 2 THEN
999 -- Return everything we find, along with relevant stats
1001 SELECT * FROM UNNEST(output_list)
1002 ORDER BY lev_distance,
1003 (soundex_sim * COALESCE(soundex_weight,0))
1004 + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
1005 + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
1006 suggestion_count DESC;
1007 ELSIF verbosity = 3 THEN
1008 -- Return everything we find from the two smallest edit distance groups
1010 SELECT * FROM UNNEST(output_list)
1011 WHERE lev_distance IN (SELECT DISTINCT lev_distance FROM UNNEST(output_list) ORDER BY 1 LIMIT 2)
1012 ORDER BY lev_distance,
1013 (soundex_sim * COALESCE(soundex_weight,0))
1014 + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
1015 + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
1016 suggestion_count DESC;
1017 ELSIF verbosity = 4 THEN
1018 -- Return everything we find from the two smallest edit distance groups that are NOT 0 distance
1020 SELECT * FROM UNNEST(output_list)
1021 WHERE lev_distance IN (SELECT DISTINCT lev_distance FROM UNNEST(output_list) WHERE lev_distance > 0 ORDER BY 1 LIMIT 2)
1022 ORDER BY lev_distance,
1023 (soundex_sim * COALESCE(soundex_weight,0))
1024 + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
1025 + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
1026 suggestion_count DESC;
1028 END LOOP; -- loop over words
1034 SELECT evergreen.upgrade_deps_block_check('1283', :eg_version); -- rhamby/ehardy/jboyer
1036 UPDATE asset.call_number SET record = -1 WHERE id = -1 AND record != -1;
1038 CREATE RULE protect_bre_id_neg1 AS ON UPDATE TO biblio.record_entry WHERE OLD.id = -1 DO INSTEAD NOTHING;
1039 CREATE RULE protect_acl_id_1 AS ON UPDATE TO asset.copy_location WHERE OLD.id = 1 DO INSTEAD NOTHING;
1040 CREATE RULE protect_acn_id_neg1 AS ON UPDATE TO asset.call_number WHERE OLD.id = -1 DO INSTEAD NOTHING;
1042 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
1044 moved_objects INT := 0;
1045 source_cn asset.call_number%ROWTYPE;
1046 target_cn asset.call_number%ROWTYPE;
1047 metarec metabib.metarecord%ROWTYPE;
1048 hold action.hold_request%ROWTYPE;
1049 ser_rec serial.record_entry%ROWTYPE;
1050 ser_sub serial.subscription%ROWTYPE;
1051 acq_lineitem acq.lineitem%ROWTYPE;
1052 acq_request acq.user_request%ROWTYPE;
1053 booking booking.resource_type%ROWTYPE;
1054 source_part biblio.monograph_part%ROWTYPE;
1055 target_part biblio.monograph_part%ROWTYPE;
1056 multi_home biblio.peer_bib_copy_map%ROWTYPE;
1060 uri_text TEXT := '';
1063 -- we don't merge bib -1
1064 IF target_record = -1 OR source_record = -1 THEN
1068 -- move any 856 entries on records that have at least one MARC-mapped URI entry
1069 SELECT INTO uri_count COUNT(*)
1070 FROM asset.uri_call_number_map m
1071 JOIN asset.call_number cn ON (m.call_number = cn.id)
1072 WHERE cn.record = source_record;
1074 IF uri_count > 0 THEN
1076 -- This returns more nodes than you might expect:
1077 -- 7 instead of 1 for an 856 with $u $y $9
1078 SELECT COUNT(*) INTO counter
1079 FROM oils_xpath_table(
1082 'biblio.record_entry',
1084 'id=' || source_record
1085 ) as t(i int,c text);
1087 FOR i IN 1 .. counter LOOP
1088 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim"' ||
1090 ' ind1="' || FIRST(ind1) || '"' ||
1091 ' ind2="' || FIRST(ind2) || '">' ||
1093 '<subfield code="' || subfield || '">' ||
1096 regexp_replace(data,'&','&','g'),
1100 ) || '</subfield>', ''
1101 ) || '</datafield>' INTO uri_datafield
1102 FROM oils_xpath_table(
1105 'biblio.record_entry',
1106 '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
1107 '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
1108 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
1109 '//*[@tag="856"][position()=' || i || ']/*[@code]',
1110 'id=' || source_record
1111 ) as t(id int,ind1 text, ind2 text,subfield text,data text);
1113 -- As most of the results will be NULL, protect against NULLifying
1114 -- the valid content that we do generate
1115 uri_text := uri_text || COALESCE(uri_datafield, '');
1118 IF uri_text <> '' THEN
1119 UPDATE biblio.record_entry
1120 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
1121 WHERE id = target_record;
1126 -- Find and move metarecords to the target record
1127 SELECT INTO metarec *
1128 FROM metabib.metarecord
1129 WHERE master_record = source_record;
1132 UPDATE metabib.metarecord
1133 SET master_record = target_record,
1135 WHERE id = metarec.id;
1137 moved_objects := moved_objects + 1;
1140 -- Find call numbers attached to the source ...
1141 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
1143 SELECT INTO target_cn *
1144 FROM asset.call_number
1145 WHERE label = source_cn.label
1146 AND prefix = source_cn.prefix
1147 AND suffix = source_cn.suffix
1148 AND owning_lib = source_cn.owning_lib
1149 AND record = target_record
1152 -- ... and if there's a conflicting one on the target ...
1155 -- ... move the copies to that, and ...
1157 SET call_number = target_cn.id
1158 WHERE call_number = source_cn.id;
1160 -- ... move V holds to the move-target call number
1161 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
1163 UPDATE action.hold_request
1164 SET target = target_cn.id
1167 moved_objects := moved_objects + 1;
1170 UPDATE asset.call_number SET deleted = TRUE WHERE id = source_cn.id;
1174 -- ... just move the call number to the target record
1175 UPDATE asset.call_number
1176 SET record = target_record
1177 WHERE id = source_cn.id;
1180 moved_objects := moved_objects + 1;
1183 -- Find T holds targeting the source record ...
1184 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
1186 -- ... and move them to the target record
1187 UPDATE action.hold_request
1188 SET target = target_record
1191 moved_objects := moved_objects + 1;
1194 -- Find serial records targeting the source record ...
1195 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
1196 -- ... and move them to the target record
1197 UPDATE serial.record_entry
1198 SET record = target_record
1199 WHERE id = ser_rec.id;
1201 moved_objects := moved_objects + 1;
1204 -- Find serial subscriptions targeting the source record ...
1205 FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP
1206 -- ... and move them to the target record
1207 UPDATE serial.subscription
1208 SET record_entry = target_record
1209 WHERE id = ser_sub.id;
1211 moved_objects := moved_objects + 1;
1214 -- Find booking resource types targeting the source record ...
1215 FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP
1216 -- ... and move them to the target record
1217 UPDATE booking.resource_type
1218 SET record = target_record
1219 WHERE id = booking.id;
1221 moved_objects := moved_objects + 1;
1224 -- Find acq lineitems targeting the source record ...
1225 FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP
1226 -- ... and move them to the target record
1228 SET eg_bib_id = target_record
1229 WHERE id = acq_lineitem.id;
1231 moved_objects := moved_objects + 1;
1234 -- Find acq user purchase requests targeting the source record ...
1235 FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP
1236 -- ... and move them to the target record
1237 UPDATE acq.user_request
1238 SET eg_bib = target_record
1239 WHERE id = acq_request.id;
1241 moved_objects := moved_objects + 1;
1244 -- Find parts attached to the source ...
1245 FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP
1247 SELECT INTO target_part *
1248 FROM biblio.monograph_part
1249 WHERE label = source_part.label
1250 AND record = target_record;
1252 -- ... and if there's a conflicting one on the target ...
1255 -- ... move the copy-part maps to that, and ...
1256 UPDATE asset.copy_part_map
1257 SET part = target_part.id
1258 WHERE part = source_part.id;
1260 -- ... move P holds to the move-target part
1261 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP
1263 UPDATE action.hold_request
1264 SET target = target_part.id
1267 moved_objects := moved_objects + 1;
1272 -- ... just move the part to the target record
1273 UPDATE biblio.monograph_part
1274 SET record = target_record
1275 WHERE id = source_part.id;
1278 moved_objects := moved_objects + 1;
1281 -- Find multi_home items attached to the source ...
1282 FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP
1283 -- ... and move them to the target record
1284 UPDATE biblio.peer_bib_copy_map
1285 SET peer_record = target_record
1286 WHERE id = multi_home.id;
1288 moved_objects := moved_objects + 1;
1291 -- And delete mappings where the item's home bib was merged with the peer bib
1292 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = (
1293 SELECT (SELECT record FROM asset.call_number WHERE id = call_number)
1294 FROM asset.copy WHERE id = target_copy
1297 -- Apply merge tracking
1298 UPDATE biblio.record_entry
1299 SET merge_date = NOW() WHERE id = target_record;
1301 UPDATE biblio.record_entry
1302 SET merge_date = NOW(), merged_to = target_record
1303 WHERE id = source_record;
1305 -- replace book bag entries of source_record with target_record
1306 UPDATE container.biblio_record_entry_bucket_item
1307 SET target_biblio_record_entry = target_record
1308 WHERE bucket IN (SELECT id FROM container.biblio_record_entry_bucket WHERE btype = 'bookbag')
1309 AND target_biblio_record_entry = source_record;
1311 -- Finally, "delete" the source record
1312 UPDATE biblio.record_entry SET active = FALSE WHERE id = source_record;
1313 DELETE FROM biblio.record_entry WHERE id = source_record;
1315 -- That's all, folks!
1316 RETURN moved_objects;
1318 $func$ LANGUAGE plpgsql;
1323 SELECT evergreen.upgrade_deps_block_check('1284', :eg_version); -- blake / terranm / jboyer
1325 INSERT INTO config.org_unit_setting_type
1326 ( name, grp, label, description, datatype, fm_class ) VALUES
1327 ( 'circ.void_item_deposit', 'circ',
1328 oils_i18n_gettext('circ.void_item_deposit',
1329 'Void item deposit fee on checkin',
1331 oils_i18n_gettext('circ.void_item_deposit',
1332 'If a deposit was charged when checking out an item, void it when the item is returned',
1333 'coust', 'description'),
1338 SELECT evergreen.upgrade_deps_block_check('1285', :eg_version);
1340 INSERT into config.org_unit_setting_type
1341 (name, grp, label, description, datatype)
1343 'circ.primary_item_value_field',
1346 'circ.primary_item_value_field',
1347 'Use Item Price or Cost as Primary Item Value',
1352 'circ.primary_item_value_field',
1353 'Expects "price" or "cost" and defaults to price. This refers to the corresponding field on the item record and gets used in such contexts as notices, max fine values when using item price caps (setting or fine rules), and long overdue, damaged, and lost billings.',
1360 INSERT into config.org_unit_setting_type
1361 (name, grp, label, description, datatype)
1363 'circ.secondary_item_value_field',
1366 'circ.secondary_item_value_field',
1367 'Use Item Price or Cost as Backup Item Value',
1372 'circ.secondary_item_value_field',
1373 'Expects "price" or "cost", but defaults to neither. This refers to the corresponding field on the item record and is used as a second-pass fall-through value when determining an item value. If needed, Evergreen will still look at the "Default Item Price" setting as a final fallback.',
1381 SELECT evergreen.upgrade_deps_block_check('1286', :eg_version);
1383 INSERT INTO config.org_unit_setting_type
1384 ( name, grp, label, description, datatype )
1386 ( 'eg.staffcat.search_filters', 'gui',
1388 'eg.staffcat.search_filters',
1389 'Staff Catalog Search Filters',
1392 'eg.staffcat.search_filters',
1393 'Array of advanced search filters to display, e.g. ["item_lang","audience","lit_form"]',
1394 'coust', 'description'),
1401 SELECT evergreen.upgrade_deps_block_check('1287', :eg_version);
1403 INSERT into config.org_unit_setting_type
1404 ( name, grp, label, description, datatype, fm_class ) VALUES
1405 ( 'lib.my_account_url', 'lib',
1406 oils_i18n_gettext('lib.my_account_url',
1407 'My Account URL (such as "https://example.com/eg/opac/login")',
1409 oils_i18n_gettext('lib.my_account_url',
1410 'URL for a My Account link. Use a complete URL, such as "https://example.com/eg/opac/login".',
1411 'coust', 'description'),
1416 SELECT evergreen.upgrade_deps_block_check('1288', :eg_version);
1418 -- stage a copy of notes, temporarily setting
1419 -- the id to the negative value for later ausp
1421 CREATE TABLE actor.XXXX_penalty_notes AS
1422 SELECT id * -1 AS id, usr, org_unit, set_date, note
1423 FROM actor.usr_standing_penalty
1424 WHERE NULLIF(BTRIM(note),'') IS NOT NULL;
1426 ALTER TABLE actor.usr_standing_penalty ALTER COLUMN id SET DEFAULT nextval('actor.usr_message_id_seq'::regclass);
1427 ALTER TABLE actor.usr_standing_penalty ADD COLUMN usr_message BIGINT REFERENCES actor.usr_message(id);
1428 CREATE INDEX usr_standing_penalty_usr_message_idx ON actor.usr_standing_penalty (usr_message);
1429 ALTER TABLE actor.usr_standing_penalty DROP COLUMN note;
1431 -- munge ausp IDs and aum IDs so that they're disjoint sets
1432 UPDATE actor.usr_standing_penalty SET id = id * -1; -- move them out of the way to avoid mid-statement collisions
1434 WITH messages AS ( SELECT COALESCE(MAX(id), 0) AS max_id FROM actor.usr_message )
1435 UPDATE actor.usr_standing_penalty SET id = id * -1 + messages.max_id FROM messages;
1437 -- doing the same thing to the staging table because
1438 -- we had to grab a copy of ausp.note first. We had
1439 -- to grab that copy first because we're both ALTERing
1440 -- and UPDATEing ausp, and all of the ALTER TABLEs
1441 -- have to be done before we can modify data in the table
1442 -- lest ALTER TABLE gets blocked by a pending trigger
1444 WITH messages AS ( SELECT COALESCE(MAX(id), 0) AS max_id FROM actor.usr_message )
1445 UPDATE actor.XXXX_penalty_notes SET id = id * -1 + messages.max_id FROM messages;
1447 SELECT SETVAL('actor.usr_message_id_seq'::regclass, COALESCE((SELECT MAX(id) FROM actor.usr_standing_penalty) + 1, 1), FALSE);
1449 ALTER TABLE actor.usr_message ADD COLUMN pub BOOL NOT NULL DEFAULT FALSE;
1450 ALTER TABLE actor.usr_message ADD COLUMN stop_date TIMESTAMP WITH TIME ZONE;
1451 ALTER TABLE actor.usr_message ADD COLUMN editor BIGINT REFERENCES actor.usr (id);
1452 ALTER TABLE actor.usr_message ADD COLUMN edit_date TIMESTAMP WITH TIME ZONE;
1454 DROP VIEW actor.usr_message_limited;
1455 CREATE VIEW actor.usr_message_limited
1456 AS SELECT * FROM actor.usr_message WHERE pub AND NOT deleted;
1458 -- alright, let's set all existing user messages to public
1460 UPDATE actor.usr_message SET pub = TRUE;
1462 -- alright, let's migrate penalty notes to usr_messages and link the messages back to the penalties:
1464 -- here is our staging table which will be shaped exactly like
1465 -- actor.usr_message and use the same id sequence
1466 CREATE TABLE actor.XXXX_usr_message_for_penalty_notes (
1467 LIKE actor.usr_message INCLUDING DEFAULTS
1470 INSERT INTO actor.XXXX_usr_message_for_penalty_notes (
1479 'Penalty Note ID ' || id,
1485 actor.XXXX_penalty_notes
1488 -- so far so good, let's push this into production
1490 INSERT INTO actor.usr_message
1491 SELECT * FROM actor.XXXX_usr_message_for_penalty_notes;
1493 -- and link the production penalties to these new user messages
1495 UPDATE actor.usr_standing_penalty p SET usr_message = m.id
1496 FROM actor.XXXX_usr_message_for_penalty_notes m
1497 WHERE m.title = 'Penalty Note ID ' || p.id;
1499 -- and remove the temporary overloading of the message title we used for this:
1506 id IN (SELECT id FROM actor.XXXX_usr_message_for_penalty_notes)
1509 -- probably redundant here, but the spec calls for an assertion before removing
1510 -- the note column from actor.usr_standing_penalty, so being extra cautious:
1515 from actor.XXXX_usr_message_for_penalty_notes
1517 select id from actor.usr_message
1519 ) = 0, 'failed migrating to actor.usr_message';
1523 -- combined view of actor.usr_standing_penalty and actor.usr_message for populating
1524 -- staff Notes (formerly Messages) interface
1526 CREATE VIEW actor.usr_message_penalty AS
1527 SELECT -- ausp with or without messages
1529 ausp.id AS "ausp_id",
1531 ausp.org_unit AS "org_unit",
1532 ausp.org_unit AS "ausp_org_unit",
1533 aum.sending_lib AS "aum_sending_lib",
1535 ausp.usr as "ausp_usr",
1536 aum.usr as "aum_usr",
1537 ausp.standing_penalty AS "standing_penalty",
1538 ausp.staff AS "staff",
1539 ausp.set_date AS "create_date",
1540 ausp.set_date AS "ausp_set_date",
1541 aum.create_date AS "aum_create_date",
1542 ausp.stop_date AS "stop_date",
1543 ausp.stop_date AS "ausp_stop_date",
1544 aum.stop_date AS "aum_stop_date",
1545 ausp.usr_message AS "ausp_usr_message",
1546 aum.title AS "title",
1547 aum.message AS "message",
1548 aum.deleted AS "deleted",
1549 aum.read_date AS "read_date",
1551 aum.editor AS "editor",
1552 aum.edit_date AS "edit_date"
1554 actor.usr_standing_penalty ausp
1555 LEFT JOIN actor.usr_message aum ON (ausp.usr_message = aum.id)
1557 SELECT -- aum without penalties
1559 NULL::INT AS "ausp_id",
1561 aum.sending_lib AS "org_unit",
1562 NULL::INT AS "ausp_org_unit",
1563 aum.sending_lib AS "aum_sending_lib",
1565 NULL::INT as "ausp_usr",
1566 aum.usr as "aum_usr",
1567 NULL::INT AS "standing_penalty",
1568 NULL::INT AS "staff",
1569 aum.create_date AS "create_date",
1570 NULL::TIMESTAMPTZ AS "ausp_set_date",
1571 aum.create_date AS "aum_create_date",
1572 aum.stop_date AS "stop_date",
1573 NULL::TIMESTAMPTZ AS "ausp_stop_date",
1574 aum.stop_date AS "aum_stop_date",
1575 NULL::INT AS "ausp_usr_message",
1576 aum.title AS "title",
1577 aum.message AS "message",
1578 aum.deleted AS "deleted",
1579 aum.read_date AS "read_date",
1581 aum.editor AS "editor",
1582 aum.edit_date AS "edit_date"
1584 actor.usr_message aum
1585 LEFT JOIN actor.usr_standing_penalty ausp ON (ausp.usr_message = aum.id)
1586 WHERE NOT aum.deleted AND ausp.id IS NULL
1589 -- fun part where we migrate the following alert messages:
1591 CREATE TABLE actor.XXXX_note_and_message_consolidation AS
1592 SELECT id, home_ou, alert_message
1594 WHERE NOT deleted AND NULLIF(BTRIM(alert_message),'') IS NOT NULL;
1596 -- here is our staging table which will be shaped exactly like
1597 -- actor.usr_message and use the same id sequence
1598 CREATE TABLE actor.XXXX_usr_message (
1599 LIKE actor.usr_message INCLUDING DEFAULTS
1602 INSERT INTO actor.XXXX_usr_message (
1611 'converted Alert Message, real date unknown',
1613 NOW(), -- best we can do
1614 1, -- it's this or home_ou
1617 actor.XXXX_note_and_message_consolidation
1620 -- another staging table, but for actor.usr_standing_penalty
1621 CREATE TABLE actor.XXXX_usr_standing_penalty (
1622 LIKE actor.usr_standing_penalty INCLUDING DEFAULTS
1625 INSERT INTO actor.XXXX_usr_standing_penalty (
1636 1, -- admin user, usually; best we can do
1640 actor.XXXX_usr_message
1643 -- so far so good, let's push these into production
1645 INSERT INTO actor.usr_message
1646 SELECT * FROM actor.XXXX_usr_message;
1647 INSERT INTO actor.usr_standing_penalty
1648 SELECT * FROM actor.XXXX_usr_standing_penalty;
1650 -- probably redundant here, but the spec calls for an assertion before removing
1651 -- the alert message column from actor.usr, so being extra cautious:
1656 from actor.XXXX_usr_message
1658 select id from actor.usr_message
1660 ) = 0, 'failed migrating to actor.usr_message';
1666 from actor.XXXX_usr_standing_penalty
1668 select id from actor.usr_standing_penalty
1670 ) = 0, 'failed migrating to actor.usr_standing_penalty';
1674 -- WARNING: we're going to lose the history of alert_message
1675 ALTER TABLE actor.usr DROP COLUMN alert_message CASCADE;
1676 SELECT auditor.update_auditors();
1678 -- fun part where we migrate actor.usr_notes as penalties to preserve
1679 -- their creator, and then the private ones to private user messages.
1680 -- For public notes, we try to link to existing user messages if we
1681 -- can, but if we can't, we'll create new, but archived, user messages
1682 -- for the note contents.
1684 CREATE TABLE actor.XXXX_usr_message_for_private_notes (
1685 LIKE actor.usr_message INCLUDING DEFAULTS
1687 ALTER TABLE actor.XXXX_usr_message_for_private_notes ADD COLUMN orig_id BIGINT;
1688 CREATE INDEX ON actor.XXXX_usr_message_for_private_notes (orig_id);
1690 INSERT INTO actor.XXXX_usr_message_for_private_notes (
1704 (select home_ou from actor.usr where id = creator), -- best we can do
1712 CREATE TABLE actor.XXXX_usr_message_for_unmatched_public_notes (
1713 LIKE actor.usr_message INCLUDING DEFAULTS
1715 ALTER TABLE actor.XXXX_usr_message_for_unmatched_public_notes ADD COLUMN orig_id BIGINT;
1716 CREATE INDEX ON actor.XXXX_usr_message_for_unmatched_public_notes (orig_id);
1718 INSERT INTO actor.XXXX_usr_message_for_unmatched_public_notes (
1733 TRUE, -- the patron has likely already seen and deleted the corresponding usr_message
1734 (select home_ou from actor.usr where id = creator), -- best we can do
1739 pub AND NOT EXISTS (SELECT 1 FROM actor.usr_message m WHERE n.usr = m.usr AND n.create_date = m.create_date)
1742 -- now, in order to preserve the creator from usr_note, we want to create standing SILENT_NOTE penalties for
1743 -- 1) actor.XXXX_usr_message_for_private_notes and associated usr_note entries
1744 -- 2) actor.XXXX_usr_message_for_unmatched_public_notes and associated usr_note entries, but archive these
1745 -- 3) usr_note and usr_message entries that can be matched
1747 CREATE TABLE actor.XXXX_usr_standing_penalties_for_notes (
1748 LIKE actor.usr_standing_penalty INCLUDING DEFAULTS
1751 -- 1) actor.XXXX_usr_message_for_private_notes and associated usr_note entries
1752 INSERT INTO actor.XXXX_usr_standing_penalties_for_notes (
1770 actor.XXXX_usr_message_for_private_notes m
1772 n.usr = m.usr AND n.id = m.orig_id AND NOT n.pub AND NOT m.pub
1775 -- 2) actor.XXXX_usr_message_for_unmatched_public_notes and associated usr_note entries, but archive these
1776 INSERT INTO actor.XXXX_usr_standing_penalties_for_notes (
1794 actor.XXXX_usr_message_for_unmatched_public_notes m
1796 n.usr = m.usr AND n.id = m.orig_id AND n.pub AND m.pub
1799 -- 3) usr_note and usr_message entries that can be matched
1800 INSERT INTO actor.XXXX_usr_standing_penalties_for_notes (
1818 JOIN actor.usr_message m ON (n.usr = m.usr AND n.id = m.id)
1820 NOT EXISTS ( SELECT 1 FROM actor.XXXX_usr_message_for_private_notes WHERE id = m.id )
1821 AND NOT EXISTS ( SELECT 1 FROM actor.XXXX_usr_message_for_unmatched_public_notes WHERE id = m.id )
1824 -- so far so good, let's push these into production
1826 INSERT INTO actor.usr_message
1827 SELECT id, usr, title, message, create_date, deleted, read_date, sending_lib, pub, stop_date, editor, edit_date FROM actor.XXXX_usr_message_for_private_notes
1828 UNION SELECT id, usr, title, message, create_date, deleted, read_date, sending_lib, pub, stop_date, editor, edit_date FROM actor.XXXX_usr_message_for_unmatched_public_notes;
1829 INSERT INTO actor.usr_standing_penalty
1830 SELECT * FROM actor.XXXX_usr_standing_penalties_for_notes;
1832 -- probably redundant here, but the spec calls for an assertion before dropping
1833 -- the actor.usr_note table, so being extra cautious:
1838 from actor.XXXX_usr_message_for_private_notes
1840 select id from actor.usr_message
1842 ) = 0, 'failed migrating to actor.usr_message';
1846 DROP TABLE actor.usr_note CASCADE;
1848 -- preserve would-be collisions for migrating
1849 -- ui.staff.require_initials.patron_info_notes
1850 -- to ui.staff.require_initials.patron_standing_penalty
1852 \o ui.staff.require_initials.patron_info_notes.collisions.txt
1854 FROM actor.org_unit_setting a
1856 a.name = 'ui.staff.require_initials.patron_info_notes'
1860 FROM actor.org_unit_setting b
1861 WHERE b.name = 'ui.staff.require_initials.patron_standing_penalty'
1863 -- but doesn't hit on org_unit + value
1864 AND CONCAT_WS('|',a.org_unit::TEXT,a.value::TEXT) NOT IN (
1865 SELECT CONCAT_WS('|',b.org_unit::TEXT,b.value::TEXT)
1866 FROM actor.org_unit_setting b
1867 WHERE b.name = 'ui.staff.require_initials.patron_standing_penalty'
1871 -- and preserve the _log data
1873 \o ui.staff.require_initials.patron_info_notes.log_data.txt
1875 FROM config.org_unit_setting_type_log
1876 WHERE field_name = 'ui.staff.require_initials.patron_info_notes';
1879 -- migrate the non-collisions
1881 INSERT INTO actor.org_unit_setting (org_unit, name, value)
1882 SELECT a.org_unit, 'ui.staff.require_initials.patron_standing_penalty', a.value
1883 FROM actor.org_unit_setting a
1885 a.name = 'ui.staff.require_initials.patron_info_notes'
1886 AND a.org_unit NOT IN (
1888 FROM actor.org_unit_setting b
1889 WHERE b.name = 'ui.staff.require_initials.patron_standing_penalty'
1893 -- and now delete the old patron_info_notes settings
1895 DELETE FROM actor.org_unit_setting
1896 WHERE name = 'ui.staff.require_initials.patron_info_notes';
1897 DELETE FROM config.org_unit_setting_type_log
1898 WHERE field_name = 'ui.staff.require_initials.patron_info_notes';
1899 DELETE FROM config.org_unit_setting_type
1900 WHERE name = 'ui.staff.require_initials.patron_info_notes';
1902 -- relabel the org unit setting type
1904 UPDATE config.org_unit_setting_type
1906 label = oils_i18n_gettext('ui.staff.require_initials.patron_standing_penalty',
1907 'Require staff initials for entry/edit of patron standing penalties and notes.',
1909 description = oils_i18n_gettext('ui.staff.require_initials.patron_standing_penalty',
1910 'Require staff initials for entry/edit of patron standing penalties and notes.',
1911 'coust', 'description')
1913 name = 'ui.staff.require_initials.patron_standing_penalty'
1916 -- preserve _log data for some different settings on their way out
1918 \o ui.patron.edit.au.alert_message.show_suggest.log_data.txt
1920 FROM config.org_unit_setting_type_log
1921 WHERE field_name IN (
1922 'ui.patron.edit.au.alert_message.show',
1923 'ui.patron.edit.au.alert_message.suggest'
1927 -- remove patron editor alert message settings
1929 DELETE FROM actor.org_unit_setting
1930 WHERE name = 'ui.patron.edit.au.alert_message.show';
1931 DELETE FROM config.org_unit_setting_type_log
1932 WHERE field_name = 'ui.patron.edit.au.alert_message.show';
1933 DELETE FROM config.org_unit_setting_type
1934 WHERE name = 'ui.patron.edit.au.alert_message.show';
1936 DELETE FROM actor.org_unit_setting
1937 WHERE name = 'ui.patron.edit.au.alert_message.suggest';
1938 DELETE FROM config.org_unit_setting_type_log
1939 WHERE field_name = 'ui.patron.edit.au.alert_message.suggest';
1940 DELETE FROM config.org_unit_setting_type
1941 WHERE name = 'ui.patron.edit.au.alert_message.suggest';
1943 -- comment these out if you want the staging tables to stick around
1944 DROP TABLE actor.XXXX_note_and_message_consolidation;
1945 DROP TABLE actor.XXXX_penalty_notes;
1946 DROP TABLE actor.XXXX_usr_message_for_penalty_notes;
1947 DROP TABLE actor.XXXX_usr_message;
1948 DROP TABLE actor.XXXX_usr_standing_penalty;
1949 DROP TABLE actor.XXXX_usr_message_for_private_notes;
1950 DROP TABLE actor.XXXX_usr_message_for_unmatched_public_notes;
1951 DROP TABLE actor.XXXX_usr_standing_penalties_for_notes;
1955 SELECT evergreen.upgrade_deps_block_check('1289', :eg_version);
1958 ALTER TABLE biblio.record_note ADD COLUMN deleted BOOLEAN DEFAULT FALSE;
1960 INSERT INTO permission.perm_list ( id, code, description ) VALUES
1961 ( 633, 'CREATE_RECORD_NOTE', oils_i18n_gettext(633,
1962 'Allow the user to create a record note', 'ppl', 'description')),
1963 ( 634, 'UPDATE_RECORD_NOTE', oils_i18n_gettext(634,
1964 'Allow the user to update a record note', 'ppl', 'description')),
1965 ( 635, 'DELETE_RECORD_NOTE', oils_i18n_gettext(635,
1966 'Allow the user to delete a record note', 'ppl', 'description'));
1968 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
1970 'eg.grid.catalog.record.notes', 'gui', 'object',
1972 'eg.grid.catalog.record.notes',
1973 'Grid Config: eg.grid.catalog.record.notes',
1979 SELECT evergreen.upgrade_deps_block_check('1290', :eg_version);
1981 -- Add an active flag column
1983 ALTER TABLE acq.funding_source ADD COLUMN active BOOL;
1985 UPDATE acq.funding_source SET active = 't';
1987 ALTER TABLE acq.funding_source ALTER COLUMN active SET DEFAULT TRUE;
1988 ALTER TABLE acq.funding_source ALTER COLUMN active SET NOT NULL;
1991 SELECT evergreen.upgrade_deps_block_check('1291', :eg_version);
1993 -- context_usr_path TEXT, -- for optimizing action_trigger.event
1994 -- context_library_path TEXT, -- '''
1995 -- context_bib_path TEXT, -- '''
1996 ALTER TABLE action_trigger.event_definition ADD COLUMN context_usr_path TEXT;
1997 ALTER TABLE action_trigger.event_definition ADD COLUMN context_library_path TEXT;
1998 ALTER TABLE action_trigger.event_definition ADD COLUMN context_bib_path TEXT;
2000 -- context_user INT REFERENCES actor.usr (id),
2001 -- context_library INT REFERENCES actor.org_unit (id),
2002 -- context_bib BIGINT REFERENCES biblio.record_entry (id)
2003 ALTER TABLE action_trigger.event ADD COLUMN context_user INT REFERENCES actor.usr (id);
2004 ALTER TABLE action_trigger.event ADD COLUMN context_library INT REFERENCES actor.org_unit (id);
2005 ALTER TABLE action_trigger.event ADD COLUMN context_bib BIGINT REFERENCES biblio.record_entry (id);
2006 CREATE INDEX atev_context_user ON action_trigger.event (context_user);
2007 CREATE INDEX atev_context_library ON action_trigger.event (context_library);
2010 action_trigger.event_definition
2012 context_usr_path = 'usr',
2013 context_library_path = 'circ_lib',
2014 context_bib_path = 'target_copy.call_number.record'
2017 SELECT key FROM action_trigger.hook WHERE core_type = 'circ'
2022 action_trigger.event_definition
2024 context_usr_path = 'usr',
2025 context_library_path = 'pickup_lib',
2026 context_bib_path = 'bib_rec'
2029 SELECT key FROM action_trigger.hook WHERE core_type = 'ahr'
2033 -- Retroactively setting context_user and context_library on existing rows in action_trigger.event:
2034 -- This is not done by default because it'll likely take a long time depending on the Evergreen
2035 -- installation. You may want to do this out-of-band with the upgrade if you want to do this at all.
2037 -- \pset format unaligned
2039 -- \o update_action_trigger_events_for_circs.sql
2040 -- SELECT 'UPDATE action_trigger.event e SET context_user = c.usr, context_library = c.circ_lib, context_bib = cn.record FROM action.circulation c, asset.copy i, asset.call_number cn WHERE c.id = e.target AND c.target_copy = i.id AND i.call_number = cn.id AND e.id = ' || e.id || ' RETURNING ' || e.id || ';' FROM action_trigger.event e, action.circulation c WHERE e.target = c.id AND e.event_def IN (SELECT id FROM action_trigger.event_definition WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'circ')) ORDER BY e.id DESC;
2042 -- \o update_action_trigger_events_for_holds.sql
2043 -- SELECT 'UPDATE action_trigger.event e SET context_user = h.usr, context_library = h.pickup_lib, context_bib = r.bib_record FROM action.hold_request h, reporter.hold_request_record r WHERE h.id = e.target AND h.id = r.id AND e.id = ' || e.id || ' RETURNING ' || e.id || ';' FROM action_trigger.event e, action.hold_request h WHERE e.target = h.id AND e.event_def IN (SELECT id FROM action_trigger.event_definition WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'ahr')) ORDER BY e.id DESC;
2048 SELECT evergreen.upgrade_deps_block_check('1292', :eg_version);
2050 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
2055 -- If there are any renewals for this circulation, don't archive or delete
2056 -- it yet. We'll do so later, when we archive and delete the renewals.
2058 SELECT 'Y' INTO found
2059 FROM action.circulation
2060 WHERE parent_circ = OLD.id
2064 RETURN NULL; -- don't delete
2067 -- Archive a copy of the old row to action.aged_circulation
2069 INSERT INTO action.aged_circulation
2070 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
2071 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
2072 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
2073 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
2074 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
2075 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
2076 auto_renewal, auto_renewal_remaining)
2078 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
2079 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
2080 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
2081 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
2082 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
2083 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
2084 auto_renewal, auto_renewal_remaining
2085 FROM action.all_circulation WHERE id = OLD.id;
2087 -- Migrate billings and payments to aged tables
2089 SELECT 'Y' INTO found FROM config.global_flag
2090 WHERE name = 'history.money.age_with_circs' AND enabled;
2093 PERFORM money.age_billings_and_payments_for_xact(OLD.id);
2096 -- Break the link with the user in action_trigger.event (warning: event_output may essentially have this information)
2098 action_trigger.event e
2102 action.all_circulation c
2105 AND e.context_user = c.usr
2107 AND e.event_def IN (
2109 FROM action_trigger.event_definition
2110 WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'circ')
2116 $$ LANGUAGE 'plpgsql';
2118 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
2120 specified_dest_usr IN INTEGER
2121 ) RETURNS VOID AS $$
2124 renamable_row RECORD;
2128 IF specified_dest_usr IS NULL THEN
2129 dest_usr := 1; -- Admin user on stock installs
2131 dest_usr := specified_dest_usr;
2134 -- action_trigger.event (even doing this, event_output may--and probably does--contain PII and should have a retention/removal policy)
2135 UPDATE action_trigger.event SET context_user = dest_usr WHERE context_user = src_usr;
2138 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
2139 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
2140 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
2141 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
2142 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
2143 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
2144 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
2145 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
2147 -- Update with a rename to avoid collisions
2148 FOR renamable_row in
2151 WHERE owner = src_usr
2153 suffix := ' (' || src_usr || ')';
2157 SET owner = dest_usr, name = name || suffix
2158 WHERE id = renamable_row.id;
2159 EXCEPTION WHEN unique_violation THEN
2160 suffix := suffix || ' ';
2167 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
2168 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
2169 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
2170 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
2171 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
2172 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
2173 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
2174 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
2177 DELETE FROM action.circulation WHERE usr = src_usr;
2178 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
2179 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
2180 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
2181 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
2182 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
2183 DELETE FROM action.hold_request WHERE usr = src_usr;
2184 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
2185 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
2186 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
2187 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
2188 DELETE FROM action.survey_response WHERE usr = src_usr;
2189 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
2190 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
2193 DELETE FROM actor.card WHERE usr = src_usr;
2194 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
2195 DELETE FROM actor.usr_privacy_waiver WHERE usr = src_usr;
2197 -- The following update is intended to avoid transient violations of a foreign
2198 -- key constraint, whereby actor.usr_address references itself. It may not be
2199 -- necessary, but it does no harm.
2200 UPDATE actor.usr_address SET replaces = NULL
2201 WHERE usr = src_usr AND replaces IS NOT NULL;
2202 DELETE FROM actor.usr_address WHERE usr = src_usr;
2203 DELETE FROM actor.usr_note WHERE usr = src_usr;
2204 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
2205 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
2206 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
2207 DELETE FROM actor.usr_setting WHERE usr = src_usr;
2208 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
2209 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
2212 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
2213 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
2214 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
2215 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
2216 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
2217 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
2220 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
2221 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
2222 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
2223 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
2224 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
2225 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
2226 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
2227 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
2230 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
2231 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
2232 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
2233 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
2236 -- Update buckets with a rename to avoid collisions
2237 FOR renamable_row in
2239 FROM container.biblio_record_entry_bucket
2240 WHERE owner = src_usr
2242 suffix := ' (' || src_usr || ')';
2245 UPDATE container.biblio_record_entry_bucket
2246 SET owner = dest_usr, name = name || suffix
2247 WHERE id = renamable_row.id;
2248 EXCEPTION WHEN unique_violation THEN
2249 suffix := suffix || ' ';
2256 FOR renamable_row in
2258 FROM container.call_number_bucket
2259 WHERE owner = src_usr
2261 suffix := ' (' || src_usr || ')';
2264 UPDATE container.call_number_bucket
2265 SET owner = dest_usr, name = name || suffix
2266 WHERE id = renamable_row.id;
2267 EXCEPTION WHEN unique_violation THEN
2268 suffix := suffix || ' ';
2275 FOR renamable_row in
2277 FROM container.copy_bucket
2278 WHERE owner = src_usr
2280 suffix := ' (' || src_usr || ')';
2283 UPDATE container.copy_bucket
2284 SET owner = dest_usr, name = name || suffix
2285 WHERE id = renamable_row.id;
2286 EXCEPTION WHEN unique_violation THEN
2287 suffix := suffix || ' ';
2294 FOR renamable_row in
2296 FROM container.user_bucket
2297 WHERE owner = src_usr
2299 suffix := ' (' || src_usr || ')';
2302 UPDATE container.user_bucket
2303 SET owner = dest_usr, name = name || suffix
2304 WHERE id = renamable_row.id;
2305 EXCEPTION WHEN unique_violation THEN
2306 suffix := suffix || ' ';
2313 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
2316 DELETE FROM money.billable_xact WHERE usr = src_usr;
2317 DELETE FROM money.collections_tracker WHERE usr = src_usr;
2318 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
2321 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
2322 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
2323 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
2324 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
2327 -- Update with a rename to avoid collisions
2329 FOR renamable_row in
2331 FROM reporter.output_folder
2332 WHERE owner = src_usr
2334 suffix := ' (' || src_usr || ')';
2337 UPDATE reporter.output_folder
2338 SET owner = dest_usr, name = name || suffix
2339 WHERE id = renamable_row.id;
2340 EXCEPTION WHEN unique_violation THEN
2341 suffix := suffix || ' ';
2347 EXCEPTION WHEN undefined_table THEN
2352 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
2353 EXCEPTION WHEN undefined_table THEN
2357 -- Update with a rename to avoid collisions
2359 FOR renamable_row in
2361 FROM reporter.report_folder
2362 WHERE owner = src_usr
2364 suffix := ' (' || src_usr || ')';
2367 UPDATE reporter.report_folder
2368 SET owner = dest_usr, name = name || suffix
2369 WHERE id = renamable_row.id;
2370 EXCEPTION WHEN unique_violation THEN
2371 suffix := suffix || ' ';
2377 EXCEPTION WHEN undefined_table THEN
2382 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
2383 EXCEPTION WHEN undefined_table THEN
2388 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
2389 EXCEPTION WHEN undefined_table THEN
2393 -- Update with a rename to avoid collisions
2395 FOR renamable_row in
2397 FROM reporter.template_folder
2398 WHERE owner = src_usr
2400 suffix := ' (' || src_usr || ')';
2403 UPDATE reporter.template_folder
2404 SET owner = dest_usr, name = name || suffix
2405 WHERE id = renamable_row.id;
2406 EXCEPTION WHEN unique_violation THEN
2407 suffix := suffix || ' ';
2413 EXCEPTION WHEN undefined_table THEN
2418 -- Update with a rename to avoid collisions
2419 FOR renamable_row in
2422 WHERE owner = src_usr
2424 suffix := ' (' || src_usr || ')';
2427 UPDATE vandelay.queue
2428 SET owner = dest_usr, name = name || suffix
2429 WHERE id = renamable_row.id;
2430 EXCEPTION WHEN unique_violation THEN
2431 suffix := suffix || ' ';
2438 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
2440 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
2441 -- can access the information before deletion.
2442 UPDATE actor.usr SET
2445 mailing_address = NULL,
2446 billing_address = NULL
2450 $$ LANGUAGE plpgsql;
2453 SELECT evergreen.upgrade_deps_block_check('1293', :eg_version);
2455 INSERT INTO config.workstation_setting_type
2456 (name, grp, datatype, label)
2458 'eg.grid.item.event_grid', 'gui', 'object',
2460 'eg.grid.item.event_grid',
2461 'Grid Config: item.event_grid',
2464 'eg.grid.patron.event_grid', 'gui', 'object',
2466 'eg.grid.patron.event_grid',
2467 'Grid Config: patron.event_grid',
2471 DROP TRIGGER IF EXISTS action_trigger_event_context_item_trig ON action_trigger.event;
2473 -- Create a NULLABLE version of the fake-copy-fkey trigger function.
2474 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
2478 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
2479 IF copy_id IS NOT NULL THEN
2480 PERFORM * FROM asset.copy WHERE id = copy_id;
2482 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
2487 $F$ LANGUAGE PLPGSQL;
2490 -- context_item_path TEXT, -- for optimizing action_trigger.event
2491 ALTER TABLE action_trigger.event_definition ADD COLUMN context_item_path TEXT;
2493 -- context_item BIGINT REFERENCES asset.copy (id)
2494 ALTER TABLE action_trigger.event ADD COLUMN context_item BIGINT;
2495 CREATE INDEX atev_context_item ON action_trigger.event (context_item);
2498 action_trigger.event_definition
2500 context_item_path = 'target_copy'
2503 SELECT key FROM action_trigger.hook WHERE core_type = 'circ'
2508 action_trigger.event_definition
2510 context_item_path = 'current_copy'
2513 SELECT key FROM action_trigger.hook WHERE core_type = 'ahr'
2517 -- Retroactively setting context_item on existing rows in action_trigger.event:
2518 -- This is not done by default because it'll likely take a long time depending on the Evergreen
2519 -- installation. You may want to do this out-of-band with the upgrade if you want to do this at all.
2521 -- \pset format unaligned
2523 -- \o update_action_trigger_events_for_circs.sql
2524 -- SELECT 'UPDATE action_trigger.event e SET context_item = c.target_copy FROM action.circulation c WHERE c.id = e.target AND e.id = ' || e.id || ' RETURNING ' || e.id || ';' FROM action_trigger.event e, action.circulation c WHERE e.target = c.id AND e.event_def IN (SELECT id FROM action_trigger.event_definition WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'circ')) ORDER BY e.id DESC;
2526 -- \o update_action_trigger_events_for_holds.sql
2527 -- SELECT 'UPDATE action_trigger.event e SET context_item = h.current_copy FROM action.hold_request h WHERE h.id = e.target AND e.id = ' || e.id || ' RETURNING ' || e.id || ';' FROM action_trigger.event e, action.hold_request h WHERE e.target = h.id AND e.event_def IN (SELECT id FROM action_trigger.event_definition WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'ahr')) ORDER BY e.id DESC;
2531 CREATE TRIGGER action_trigger_event_context_item_trig
2532 AFTER INSERT OR UPDATE ON action_trigger.event
2533 FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('context_item');
2536 SELECT evergreen.upgrade_deps_block_check('1294', :eg_version); -- mmorgan / tlittle / JBoyer
2538 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
2540 'eg.grid.admin.local.container.carousel_org_unit', 'gui', 'object',
2542 'eg.grid.admin.local.container.carousel_org_unit',
2543 'Grid Config: eg.grid.admin.local.container.carousel_org_unit',
2547 'eg.grid.admin.container.carousel', 'gui', 'object',
2549 'eg.grid.admin.container.carousel',
2550 'Grid Config: eg.grid.admin.container.carousel',
2554 'eg.grid.admin.server.config.carousel_type', 'gui', 'object',
2556 'eg.grid.admin.server.config.carousel_type',
2557 'Grid Config: eg.grid.admin.server.config.carousel_type',
2563 SELECT evergreen.upgrade_deps_block_check('1295', :eg_version);
2565 ALTER TABLE vandelay.merge_profile
2566 ADD COLUMN update_bib_editor BOOLEAN NOT NULL DEFAULT FALSE;
2568 -- By default, updating bib source means updating the editor.
2569 UPDATE vandelay.merge_profile SET update_bib_editor = update_bib_source;
2571 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record
2572 ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
2578 update_fields TEXT[];
2580 update_bib_source BOOL;
2581 update_bib_editor BOOL;
2584 SELECT q.marc, q.bib_source INTO v_marc, v_bib_source
2585 FROM vandelay.queued_bib_record q
2586 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
2589 IF v_marc IS NULL THEN
2590 -- RAISE NOTICE 'no marc for vandelay or bib record';
2594 IF NOT vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
2595 -- no update happened, get outta here.
2599 UPDATE vandelay.queued_bib_record
2600 SET imported_as = eg_id,
2602 WHERE id = import_id;
2604 SELECT q.update_bib_source INTO update_bib_source
2605 FROM vandelay.merge_profile q where q.id = merge_profile_Id;
2607 IF update_bib_source AND v_bib_source IS NOT NULL THEN
2608 update_fields := ARRAY_APPEND(update_fields, 'source = ' || v_bib_source);
2611 SELECT q.update_bib_editor INTO update_bib_editor
2612 FROM vandelay.merge_profile q where q.id = merge_profile_Id;
2614 IF update_bib_editor THEN
2616 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
2618 IF editor_string IS NOT NULL AND editor_string <> '' THEN
2619 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
2621 IF editor_id IS NULL THEN
2622 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
2625 IF editor_id IS NOT NULL THEN
2626 --only update the edit date if we have a valid editor
2627 update_fields := ARRAY_APPEND(
2628 update_fields, 'editor = ' || editor_id || ', edit_date = NOW()');
2633 IF ARRAY_LENGTH(update_fields, 1) > 0 THEN
2634 update_query := 'UPDATE biblio.record_entry SET ' ||
2635 ARRAY_TO_STRING(update_fields, ',') || ' WHERE id = ' || eg_id || ';';
2636 EXECUTE update_query;
2641 $$ LANGUAGE PLPGSQL;
2645 SELECT evergreen.upgrade_deps_block_check('1296', :eg_version);
2647 CREATE OR REPLACE VIEW reporter.demographic AS
2653 WHEN AGE(u.dob) > '18 years'::INTERVAL
2656 END AS general_division,
2659 THEN 'No Date of Birth Entered'::text
2660 WHEN age(u.dob::timestamp with time zone) >= '0 years'::interval and age(u.dob::timestamp with time zone) < '6 years'::interval
2661 THEN 'Child 0-5 Years Old'::text
2662 WHEN age(u.dob::timestamp with time zone) >= '6 years'::interval and age(u.dob::timestamp with time zone) < '13 years'::interval
2663 THEN 'Child 6-12 Years Old'::text
2664 WHEN age(u.dob::timestamp with time zone) >= '13 years'::interval and age(u.dob::timestamp with time zone) < '18 years'::interval
2665 THEN 'Teen 13-17 Years Old'::text
2666 WHEN age(u.dob::timestamp with time zone) >= '18 years'::interval and age(u.dob::timestamp with time zone) < '26 years'::interval
2667 THEN 'Adult 18-25 Years Old'::text
2668 WHEN age(u.dob::timestamp with time zone) >= '26 years'::interval and age(u.dob::timestamp with time zone) < '50 years'::interval
2669 THEN 'Adult 26-49 Years Old'::text
2670 WHEN age(u.dob::timestamp with time zone) >= '50 years'::interval and age(u.dob::timestamp with time zone) < '60 years'::interval
2671 THEN 'Adult 50-59 Years Old'::text
2672 WHEN age(u.dob::timestamp with time zone) >= '60 years'::interval and age(u.dob::timestamp with time zone) < '70 years'::interval
2673 THEN 'Adult 60-69 Years Old'::text
2674 WHEN age(u.dob::timestamp with time zone) >= '70 years'::interval
2675 THEN 'Adult 70+'::text
2681 SELECT evergreen.upgrade_deps_block_check('1297', :eg_version);
2683 INSERT INTO config.org_unit_setting_type (
2684 name, grp, label, description, datatype
2686 'circ.staff_placed_holds_default_to_ws_ou',
2689 'circ.staff_placed_holds_default_to_ws_ou',
2690 'Workstation OU is the default for staff-placed holds',
2695 'circ.staff_placed_holds_default_to_ws_ou',
2696 'For staff-placed holds, regardless of the patron preferred pickup location, the staff workstation OU is the default pickup location',
2704 SELECT evergreen.upgrade_deps_block_check('1298', :eg_version);
2706 ALTER TYPE metabib.field_entry_template ADD ATTRIBUTE browse_nocase BOOL CASCADE;
2708 ALTER TABLE config.metabib_field ADD COLUMN browse_nocase BOOL NOT NULL DEFAULT FALSE;
2710 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
2712 default_joiner TEXT,
2715 ) RETURNS SETOF metabib.field_entry_template AS $func$
2717 bib biblio.record_entry%ROWTYPE;
2718 idx config.metabib_field%ROWTYPE;
2719 xfrm config.xml_transform%ROWTYPE;
2721 transformed_xml TEXT;
2723 xml_node_list TEXT[];
2730 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
2731 authority_text TEXT;
2732 authority_link BIGINT;
2733 output_row metabib.field_entry_template%ROWTYPE;
2737 -- Start out with no field-use bools set
2738 output_row.browse_nocase = FALSE;
2739 output_row.browse_field = FALSE;
2740 output_row.facet_field = FALSE;
2741 output_row.display_field = FALSE;
2742 output_row.search_field = FALSE;
2745 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
2747 -- Loop over the indexing entries
2748 FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
2749 CONTINUE WHEN idx.xpath IS NULL OR idx.xpath = ''; -- pure virtual field
2751 process_idx := FALSE;
2752 IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
2753 IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
2754 IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
2755 IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
2756 CONTINUE WHEN process_idx = FALSE; -- disabled for all types
2758 joiner := COALESCE(idx.joiner, default_joiner);
2760 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
2762 -- See if we can skip the XSLT ... it's expensive
2763 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
2764 -- Can't skip the transform
2765 IF xfrm.xslt <> '---' THEN
2766 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
2768 transformed_xml := bib.marc;
2771 prev_xfrm := xfrm.name;
2774 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
2777 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
2778 CONTINUE WHEN xml_node !~ E'^\\s*<';
2780 -- XXX much of this should be moved into oils_xpath_string...
2781 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
2782 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
2783 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
2784 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
2788 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
2790 IF raw_text IS NOT NULL THEN
2791 raw_text := raw_text || joiner;
2794 raw_text := COALESCE(raw_text,'') || curr_text;
2796 -- autosuggest/metabib.browse_entry
2797 IF idx.browse_field THEN
2798 output_row.browse_nocase = idx.browse_nocase;
2800 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
2801 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
2803 browse_text := curr_text;
2806 IF idx.browse_sort_xpath IS NOT NULL AND
2807 idx.browse_sort_xpath <> '' THEN
2809 sort_value := oils_xpath_string(
2810 idx.browse_sort_xpath, xml_node, joiner,
2811 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
2814 sort_value := browse_text;
2817 output_row.field_class = idx.field_class;
2818 output_row.field = idx.id;
2819 output_row.source = rid;
2820 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
2821 output_row.sort_value :=
2822 public.naco_normalize(sort_value);
2824 output_row.authority := NULL;
2826 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
2827 authority_text := oils_xpath_string(
2828 idx.authority_xpath, xml_node, joiner,
2830 ARRAY[xfrm.prefix, xfrm.namespace_uri],
2831 ARRAY['xlink','http://www.w3.org/1999/xlink']
2835 IF authority_text ~ '^\d+$' THEN
2836 authority_link := authority_text::BIGINT;
2837 PERFORM * FROM authority.record_entry WHERE id = authority_link;
2839 output_row.authority := authority_link;
2845 output_row.browse_field = TRUE;
2846 -- Returning browse rows with search_field = true for search+browse
2847 -- configs allows us to retain granularity of being able to search
2848 -- browse fields with "starts with" type operators (for example, for
2849 -- titles of songs in music albums)
2850 IF idx.search_field THEN
2851 output_row.search_field = TRUE;
2853 RETURN NEXT output_row;
2854 output_row.browse_nocase = FALSE;
2855 output_row.browse_field = FALSE;
2856 output_row.search_field = FALSE;
2857 output_row.sort_value := NULL;
2860 -- insert raw node text for faceting
2861 IF idx.facet_field THEN
2863 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
2864 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
2866 facet_text := curr_text;
2869 output_row.field_class = idx.field_class;
2870 output_row.field = -1 * idx.id;
2871 output_row.source = rid;
2872 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
2874 output_row.facet_field = TRUE;
2875 RETURN NEXT output_row;
2876 output_row.facet_field = FALSE;
2879 -- insert raw node text for display
2880 IF idx.display_field THEN
2882 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
2883 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
2885 display_text := curr_text;
2888 output_row.field_class = idx.field_class;
2889 output_row.field = -1 * idx.id;
2890 output_row.source = rid;
2891 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
2893 output_row.display_field = TRUE;
2894 RETURN NEXT output_row;
2895 output_row.display_field = FALSE;
2900 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
2902 -- insert combined node text for searching
2903 IF idx.search_field THEN
2904 output_row.field_class = idx.field_class;
2905 output_row.field = idx.id;
2906 output_row.source = rid;
2907 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
2909 output_row.search_field = TRUE;
2910 RETURN NEXT output_row;
2911 output_row.search_field = FALSE;
2917 $func$ LANGUAGE PLPGSQL;
2919 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries(
2921 skip_facet BOOL DEFAULT FALSE,
2922 skip_display BOOL DEFAULT FALSE,
2923 skip_browse BOOL DEFAULT FALSE,
2924 skip_search BOOL DEFAULT FALSE,
2925 only_fields INT[] DEFAULT '{}'::INT[]
2926 ) RETURNS VOID AS $func$
2929 ind_data metabib.field_entry_template%ROWTYPE;
2930 mbe_row metabib.browse_entry%ROWTYPE;
2933 b_skip_display BOOL;
2937 field_list INT[] := only_fields;
2938 field_types TEXT[] := '{}'::TEXT[];
2941 IF field_list = '{}'::INT[] THEN
2942 SELECT ARRAY_AGG(id) INTO field_list FROM config.metabib_field;
2945 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;
2946 SELECT COALESCE(NULLIF(skip_display, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_display_indexing' AND enabled)) INTO b_skip_display;
2947 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;
2948 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;
2950 IF NOT b_skip_facet THEN field_types := field_types || '{facet}'; END IF;
2951 IF NOT b_skip_display THEN field_types := field_types || '{display}'; END IF;
2952 IF NOT b_skip_browse THEN field_types := field_types || '{browse}'; END IF;
2953 IF NOT b_skip_search THEN field_types := field_types || '{search}'; END IF;
2955 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
2957 IF NOT b_skip_search THEN
2958 FOR fclass IN SELECT * FROM config.metabib_class LOOP
2959 -- RAISE NOTICE 'Emptying out %', fclass.name;
2960 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
2963 IF NOT b_skip_facet THEN
2964 DELETE FROM metabib.facet_entry WHERE source = bib_id;
2966 IF NOT b_skip_display THEN
2967 DELETE FROM metabib.display_entry WHERE source = bib_id;
2969 IF NOT b_skip_browse THEN
2970 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
2974 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id, ' ', field_types, field_list ) LOOP
2976 -- don't store what has been normalized away
2977 CONTINUE WHEN ind_data.value IS NULL;
2979 IF ind_data.field < 0 THEN
2980 ind_data.field = -1 * ind_data.field;
2983 IF ind_data.facet_field AND NOT b_skip_facet THEN
2984 INSERT INTO metabib.facet_entry (field, source, value)
2985 VALUES (ind_data.field, ind_data.source, ind_data.value);
2988 IF ind_data.display_field AND NOT b_skip_display THEN
2989 INSERT INTO metabib.display_entry (field, source, value)
2990 VALUES (ind_data.field, ind_data.source, ind_data.value);
2994 IF ind_data.browse_field AND NOT b_skip_browse THEN
2995 -- A caveat about this SELECT: this should take care of replacing
2996 -- old mbe rows when data changes, but not if normalization (by
2997 -- which I mean specifically the output of
2998 -- evergreen.oils_tsearch2()) changes. It may or may not be
2999 -- expensive to add a comparison of index_vector to index_vector
3000 -- to the WHERE clause below.
3002 CONTINUE WHEN ind_data.sort_value IS NULL;
3004 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
3005 IF ind_data.browse_nocase THEN
3006 SELECT INTO mbe_row * FROM metabib.browse_entry
3007 WHERE evergreen.lowercase(value) = evergreen.lowercase(value_prepped) AND sort_value = ind_data.sort_value
3008 ORDER BY sort_value, value LIMIT 1; -- gotta pick something, I guess
3010 SELECT INTO mbe_row * FROM metabib.browse_entry
3011 WHERE value = value_prepped AND sort_value = ind_data.sort_value;
3015 mbe_id := mbe_row.id;
3017 INSERT INTO metabib.browse_entry
3018 ( value, sort_value ) VALUES
3019 ( value_prepped, ind_data.sort_value );
3021 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
3024 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
3025 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
3028 IF ind_data.search_field AND NOT b_skip_search THEN
3029 -- Avoid inserting duplicate rows
3030 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
3031 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
3032 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
3033 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
3034 IF mbe_id IS NULL THEN
3036 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
3038 quote_literal(ind_data.field) || $$, $$ ||
3039 quote_literal(ind_data.source) || $$, $$ ||
3040 quote_literal(ind_data.value) ||
3047 IF NOT b_skip_search THEN
3048 PERFORM metabib.update_combined_index_vectors(bib_id);
3053 $func$ LANGUAGE PLPGSQL;
3057 SELECT evergreen.upgrade_deps_block_check('1299', :eg_version);
3059 CREATE OR REPLACE FUNCTION vandelay.strip_field(xml text, field text) RETURNS text AS $f$
3062 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3066 MARC::Charset->assume_unicode(1);
3069 my $r = MARC::Record->new_from_xml( $xml );
3071 return $xml unless ($r);
3073 my $field_spec = shift;
3074 my @field_list = split(',', $field_spec);
3077 for my $f (@field_list) {
3078 $f =~ s/^\s*//; $f =~ s/\s*$//;
3079 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
3085 $matches =~ s/^\s*//; $matches =~ s/\s*$//;
3086 $fields{$field} = { sf => [ split('', $sf) ] };
3088 for my $match (split('&&', $matches)) {
3089 $match =~ s/^\s*//; $match =~ s/\s*$//;
3090 my ($msf,$mre) = split('~', $match);
3091 if (length($msf) > 0 and length($mre) > 0) {
3092 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
3093 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
3094 $fields{$field}{match}{$msf} = qr/$mre/;
3101 for my $f ( keys %fields) {
3102 for my $to_field ($r->field( $f )) {
3103 if (exists($fields{$f}{match})) {
3104 my @match_list = grep { $to_field->subfield($_) =~ $fields{$f}{match}{$_} } keys %{$fields{$f}{match}};
3105 next unless (scalar(@match_list) == scalar(keys %{$fields{$f}{match}}));
3108 if ( @{$fields{$f}{sf}} ) {
3109 $to_field->delete_subfield(code => $fields{$f}{sf});
3111 $r->delete_field( $to_field );
3116 $xml = $r->as_xml_record;
3117 $xml =~ s/^<\?.+?\?>$//mo;
3119 $xml =~ s/>\s+</></sgo;
3123 $f$ LANGUAGE plperlu;
3128 SELECT evergreen.upgrade_deps_block_check('1300', :eg_version);
3130 -- NOTE: If the template ID requires changing, beware it appears in
3133 INSERT INTO config.print_template
3134 (id, name, locale, active, owner, label, template)
3136 4, 'hold_pull_list', 'en-US', TRUE,
3137 (SELECT id FROM actor.org_unit WHERE parent_ou IS NULL),
3138 oils_i18n_gettext(4, 'Hold Pull List ', 'cpt', 'label'),
3142 UPDATE config.print_template SET template =
3146 SET holds = template_data;
3147 # template_data is an arry of wide_hold hashes.
3151 #holds-pull-list-table td {
3153 border: 1px solid rgba(0,0,0,.05);
3156 <table id="holds-pull-list-table">
3162 <th>Shelf Location</th>
3163 <th>Call Number</th>
3164 <th>Barcode/Part</th>
3168 [% FOR hold IN holds %]
3170 <td>[% hold.hold_type %]</td>
3171 <td style="width: 30%">[% hold.title %]</td>
3172 <td style="width: 25%">[% hold.author %]</td>
3173 <td>[% hold.acpl_name %]</td>
3174 <td>[% hold.cn_full_label %]</td>
3175 <td>[% hold.cp_barcode %][% IF hold.p_label %]/[% hold.p_label %][% END %]</td>
3181 $TEMPLATE$ WHERE id = 4;
3183 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
3185 'eg.grid.circ.holds.pull_list', 'gui', 'object',
3187 'circ.holds.pull_list',
3188 'Hold Pull List Grid Settings',
3192 'circ.holds.pull_list.prefetch', 'gui', 'bool',
3194 'circ.holds.pull_list.prefetch',
3195 'Hold Pull List Prefetch Preference',
3202 SELECT evergreen.upgrade_deps_block_check('1301', :eg_version);
3204 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
3206 default_joiner TEXT,
3209 ) RETURNS SETOF metabib.field_entry_template AS $func$
3211 bib biblio.record_entry%ROWTYPE;
3212 idx config.metabib_field%ROWTYPE;
3213 xfrm config.xml_transform%ROWTYPE;
3215 transformed_xml TEXT;
3217 xml_node_list TEXT[];
3224 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
3225 authority_text TEXT;
3226 authority_link BIGINT;
3227 output_row metabib.field_entry_template%ROWTYPE;
3231 -- Start out with no field-use bools set
3232 output_row.browse_nocase = FALSE;
3233 output_row.browse_field = FALSE;
3234 output_row.facet_field = FALSE;
3235 output_row.display_field = FALSE;
3236 output_row.search_field = FALSE;
3239 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
3241 -- Loop over the indexing entries
3242 FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
3243 CONTINUE WHEN idx.xpath IS NULL OR idx.xpath = ''; -- pure virtual field
3245 process_idx := FALSE;
3246 IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
3247 IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
3248 IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
3249 IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
3250 CONTINUE WHEN process_idx = FALSE; -- disabled for all types
3252 joiner := COALESCE(idx.joiner, default_joiner);
3254 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
3256 -- See if we can skip the XSLT ... it's expensive
3257 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
3258 -- Can't skip the transform
3259 IF xfrm.xslt <> '---' THEN
3260 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
3262 transformed_xml := bib.marc;
3265 prev_xfrm := xfrm.name;
3268 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
3271 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
3272 CONTINUE WHEN xml_node !~ E'^\\s*<';
3274 -- XXX much of this should be moved into oils_xpath_string...
3275 curr_text := ARRAY_TO_STRING(array_remove(array_remove(
3276 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
3277 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
3278 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
3282 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
3284 IF raw_text IS NOT NULL THEN
3285 raw_text := raw_text || joiner;
3288 raw_text := COALESCE(raw_text,'') || curr_text;
3290 -- autosuggest/metabib.browse_entry
3291 IF idx.browse_field THEN
3292 output_row.browse_nocase = idx.browse_nocase;
3294 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
3295 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
3297 browse_text := curr_text;
3300 IF idx.browse_sort_xpath IS NOT NULL AND
3301 idx.browse_sort_xpath <> '' THEN
3303 sort_value := oils_xpath_string(
3304 idx.browse_sort_xpath, xml_node, joiner,
3305 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
3308 sort_value := browse_text;
3311 output_row.field_class = idx.field_class;
3312 output_row.field = idx.id;
3313 output_row.source = rid;
3314 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
3315 output_row.sort_value :=
3316 public.naco_normalize(sort_value);
3318 output_row.authority := NULL;
3320 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
3321 authority_text := oils_xpath_string(
3322 idx.authority_xpath, xml_node, joiner,
3324 ARRAY[xfrm.prefix, xfrm.namespace_uri],
3325 ARRAY['xlink','http://www.w3.org/1999/xlink']
3329 IF authority_text ~ '^\d+$' THEN
3330 authority_link := authority_text::BIGINT;
3331 PERFORM * FROM authority.record_entry WHERE id = authority_link;
3333 output_row.authority := authority_link;
3339 output_row.browse_field = TRUE;
3340 -- Returning browse rows with search_field = true for search+browse
3341 -- configs allows us to retain granularity of being able to search
3342 -- browse fields with "starts with" type operators (for example, for
3343 -- titles of songs in music albums)
3344 IF idx.search_field THEN
3345 output_row.search_field = TRUE;
3347 RETURN NEXT output_row;
3348 output_row.browse_nocase = FALSE;
3349 output_row.browse_field = FALSE;
3350 output_row.search_field = FALSE;
3351 output_row.sort_value := NULL;
3354 -- insert raw node text for faceting
3355 IF idx.facet_field THEN
3357 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
3358 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
3360 facet_text := curr_text;
3363 output_row.field_class = idx.field_class;
3364 output_row.field = -1 * idx.id;
3365 output_row.source = rid;
3366 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
3368 output_row.facet_field = TRUE;
3369 RETURN NEXT output_row;
3370 output_row.facet_field = FALSE;
3373 -- insert raw node text for display
3374 IF idx.display_field THEN
3376 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
3377 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
3379 display_text := curr_text;
3382 output_row.field_class = idx.field_class;
3383 output_row.field = -1 * idx.id;
3384 output_row.source = rid;
3385 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
3387 output_row.display_field = TRUE;
3388 RETURN NEXT output_row;
3389 output_row.display_field = FALSE;
3394 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
3396 -- insert combined node text for searching
3397 IF idx.search_field THEN
3398 output_row.field_class = idx.field_class;
3399 output_row.field = idx.id;
3400 output_row.source = rid;
3401 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
3403 output_row.search_field = TRUE;
3404 RETURN NEXT output_row;
3405 output_row.search_field = FALSE;
3411 $func$ LANGUAGE PLPGSQL;
3414 SELECT evergreen.upgrade_deps_block_check('1302', :eg_version);
3416 UPDATE config.org_unit_setting_type
3417 SET description = oils_i18n_gettext(
3418 'ui.circ.items_out.longoverdue',
3419 'Value is a numeric code, describing: A. In which tab ("Items Checked Out", '||
3420 'or "Other/Special Circulations") the circulation '||
3421 'should appear while checked out, and B. Whether the circulation should '||
3422 'continue to appear in the "Other" tab when checked in with '||
3423 'outstanding fines. '||
3424 '1 = (A) "Items", (B) "Other". 2 = (A) "Other", (B) "Other". ' ||
3425 '5 = (A) "Items", (B) do not display. 6 = (A) "Other", (B) do not display.',
3429 WHERE name = 'ui.circ.items_out.longoverdue';
3431 UPDATE config.org_unit_setting_type
3432 set description = oils_i18n_gettext(
3433 'ui.circ.items_out.lost',
3434 'Value is a numeric code, describing: A. In which tab ("Items Checked Out", '||
3435 'or "Other/Special Circulations") the circulation '||
3436 'should appear while checked out, and B. Whether the circulation should '||
3437 'continue to appear in the "Other" tab when checked in with '||
3438 'outstanding fines. '||
3439 '1 = (A) "Items", (B) "Other". 2 = (A) "Other", (B) "Other". ' ||
3440 '5 = (A) "Items", (B) do not display. 6 = (A) "Other", (B) do not display.',
3444 WHERE name = 'ui.circ.items_out.lost';
3446 UPDATE config.org_unit_setting_type
3447 set description = oils_i18n_gettext(
3448 'ui.circ.items_out.claimsreturned',
3449 'Value is a numeric code, describing: A. In which tab ("Items Checked Out", '||
3450 'or "Other/Special Circulations") the circulation '||
3451 'should appear while checked out, and B. Whether the circulation should '||
3452 'continue to appear in the "Other" tab when checked in with '||
3453 'outstanding fines. '||
3454 '1 = (A) "Items", (B) "Other". 2 = (A) "Other", (B) "Other". ' ||
3455 '5 = (A) "Items", (B) do not display. 6 = (A) "Other", (B) do not display.',
3459 WHERE name = 'ui.circ.items_out.claimsreturned';
3461 SELECT evergreen.upgrade_deps_block_check('1303', :eg_version);
3463 DROP INDEX authority.authority_full_rec_value_index;
3464 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (SUBSTRING(value FOR 1024));
3466 DROP INDEX authority.authority_full_rec_value_tpo_index;
3467 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (SUBSTRING(value FOR 1024) text_pattern_ops);
3469 SELECT evergreen.upgrade_deps_block_check('1304', :eg_version);
3471 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
3473 specified_dest_usr IN INTEGER
3474 ) RETURNS VOID AS $$
3477 renamable_row RECORD;
3481 IF specified_dest_usr IS NULL THEN
3482 dest_usr := 1; -- Admin user on stock installs
3484 dest_usr := specified_dest_usr;
3487 -- action_trigger.event (even doing this, event_output may--and probably does--contain PII and should have a retention/removal policy)
3488 UPDATE action_trigger.event SET context_user = dest_usr WHERE context_user = src_usr;
3491 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
3492 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
3493 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
3494 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
3495 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
3496 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
3497 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
3498 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
3500 -- Update with a rename to avoid collisions
3501 FOR renamable_row in
3504 WHERE owner = src_usr
3506 suffix := ' (' || src_usr || ')';
3510 SET owner = dest_usr, name = name || suffix
3511 WHERE id = renamable_row.id;
3512 EXCEPTION WHEN unique_violation THEN
3513 suffix := suffix || ' ';
3520 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
3521 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
3522 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
3523 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
3524 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
3525 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
3526 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
3527 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
3530 DELETE FROM action.circulation WHERE usr = src_usr;
3531 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
3532 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
3533 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
3534 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
3535 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
3536 DELETE FROM action.hold_request WHERE usr = src_usr;
3537 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
3538 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
3539 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
3540 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
3541 DELETE FROM action.survey_response WHERE usr = src_usr;
3542 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
3543 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
3546 DELETE FROM actor.card WHERE usr = src_usr;
3547 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
3548 DELETE FROM actor.usr_privacy_waiver WHERE usr = src_usr;
3550 -- The following update is intended to avoid transient violations of a foreign
3551 -- key constraint, whereby actor.usr_address references itself. It may not be
3552 -- necessary, but it does no harm.
3553 UPDATE actor.usr_address SET replaces = NULL
3554 WHERE usr = src_usr AND replaces IS NOT NULL;
3555 DELETE FROM actor.usr_address WHERE usr = src_usr;
3556 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
3557 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
3558 DELETE FROM actor.usr_setting WHERE usr = src_usr;
3559 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
3560 UPDATE actor.usr_message SET title = 'purged', message = 'purged', read_date = NOW() WHERE usr = src_usr;
3561 DELETE FROM actor.usr_message WHERE usr = src_usr;
3562 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
3563 UPDATE actor.usr_message SET editor = dest_usr WHERE editor = src_usr;
3566 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
3567 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
3568 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
3569 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
3570 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
3571 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
3574 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
3575 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
3576 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
3577 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
3578 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
3579 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
3580 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
3581 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
3584 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
3585 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
3586 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
3587 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
3590 -- Update buckets with a rename to avoid collisions
3591 FOR renamable_row in
3593 FROM container.biblio_record_entry_bucket
3594 WHERE owner = src_usr
3596 suffix := ' (' || src_usr || ')';
3599 UPDATE container.biblio_record_entry_bucket
3600 SET owner = dest_usr, name = name || suffix
3601 WHERE id = renamable_row.id;
3602 EXCEPTION WHEN unique_violation THEN
3603 suffix := suffix || ' ';
3610 FOR renamable_row in
3612 FROM container.call_number_bucket
3613 WHERE owner = src_usr
3615 suffix := ' (' || src_usr || ')';
3618 UPDATE container.call_number_bucket
3619 SET owner = dest_usr, name = name || suffix
3620 WHERE id = renamable_row.id;
3621 EXCEPTION WHEN unique_violation THEN
3622 suffix := suffix || ' ';
3629 FOR renamable_row in
3631 FROM container.copy_bucket
3632 WHERE owner = src_usr
3634 suffix := ' (' || src_usr || ')';
3637 UPDATE container.copy_bucket
3638 SET owner = dest_usr, name = name || suffix
3639 WHERE id = renamable_row.id;
3640 EXCEPTION WHEN unique_violation THEN
3641 suffix := suffix || ' ';
3648 FOR renamable_row in
3650 FROM container.user_bucket
3651 WHERE owner = src_usr
3653 suffix := ' (' || src_usr || ')';
3656 UPDATE container.user_bucket
3657 SET owner = dest_usr, name = name || suffix
3658 WHERE id = renamable_row.id;
3659 EXCEPTION WHEN unique_violation THEN
3660 suffix := suffix || ' ';
3667 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
3670 DELETE FROM money.billable_xact WHERE usr = src_usr;
3671 DELETE FROM money.collections_tracker WHERE usr = src_usr;
3672 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
3675 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
3676 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
3677 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
3678 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
3681 -- Update with a rename to avoid collisions
3683 FOR renamable_row in
3685 FROM reporter.output_folder
3686 WHERE owner = src_usr
3688 suffix := ' (' || src_usr || ')';
3691 UPDATE reporter.output_folder
3692 SET owner = dest_usr, name = name || suffix
3693 WHERE id = renamable_row.id;
3694 EXCEPTION WHEN unique_violation THEN
3695 suffix := suffix || ' ';
3701 EXCEPTION WHEN undefined_table THEN
3706 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
3707 EXCEPTION WHEN undefined_table THEN
3711 -- Update with a rename to avoid collisions
3713 FOR renamable_row in
3715 FROM reporter.report_folder
3716 WHERE owner = src_usr
3718 suffix := ' (' || src_usr || ')';
3721 UPDATE reporter.report_folder
3722 SET owner = dest_usr, name = name || suffix
3723 WHERE id = renamable_row.id;
3724 EXCEPTION WHEN unique_violation THEN
3725 suffix := suffix || ' ';
3731 EXCEPTION WHEN undefined_table THEN
3736 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
3737 EXCEPTION WHEN undefined_table THEN
3742 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
3743 EXCEPTION WHEN undefined_table THEN
3747 -- Update with a rename to avoid collisions
3749 FOR renamable_row in
3751 FROM reporter.template_folder
3752 WHERE owner = src_usr
3754 suffix := ' (' || src_usr || ')';
3757 UPDATE reporter.template_folder
3758 SET owner = dest_usr, name = name || suffix
3759 WHERE id = renamable_row.id;
3760 EXCEPTION WHEN unique_violation THEN
3761 suffix := suffix || ' ';
3767 EXCEPTION WHEN undefined_table THEN
3772 -- Update with a rename to avoid collisions
3773 FOR renamable_row in
3776 WHERE owner = src_usr
3778 suffix := ' (' || src_usr || ')';
3781 UPDATE vandelay.queue
3782 SET owner = dest_usr, name = name || suffix
3783 WHERE id = renamable_row.id;
3784 EXCEPTION WHEN unique_violation THEN
3785 suffix := suffix || ' ';
3792 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
3794 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
3795 -- can access the information before deletion.
3796 UPDATE actor.usr SET
3799 mailing_address = NULL,
3800 billing_address = NULL
3804 $$ LANGUAGE plpgsql;
3806 CREATE OR REPLACE FUNCTION actor.usr_delete(
3809 ) RETURNS VOID AS $$
3811 old_profile actor.usr.profile%type;
3812 old_home_ou actor.usr.home_ou%type;
3813 new_profile actor.usr.profile%type;
3814 new_home_ou actor.usr.home_ou%type;
3816 new_dob actor.usr.dob%type;
3819 id || '-PURGED-' || now(),
3833 -- Quit if no such user
3835 IF old_profile IS NULL THEN
3839 perform actor.usr_purge_data( src_usr, dest_usr );
3841 -- Find the root grp_tree and the root org_unit. This would be simpler if we
3842 -- could assume that there is only one root. Theoretically, someday, maybe,
3843 -- there could be multiple roots, so we take extra trouble to get the right ones.
3850 permission.grp_ancestors( old_profile )
3859 actor.org_unit_ancestors( old_home_ou )
3863 -- Truncate date of birth
3865 IF new_dob IS NOT NULL THEN
3866 new_dob := date_trunc( 'year', new_dob );
3873 profile = new_profile,
3876 passwd = random()::text,
3881 FROM config.identification_type
3885 ident_value2 = NULL,
3886 net_access_level = DEFAULT,
3889 first_given_name = new_name,
3890 second_given_name = NULL,
3891 family_name = new_name,
3896 evening_phone = NULL,
3898 mailing_address = NULL,
3899 billing_address = NULL,
3900 home_ou = new_home_ou,
3903 master_account = DEFAULT,
3904 super_user = DEFAULT,
3909 claims_returned_count = DEFAULT,
3910 credit_forward_balance = DEFAULT,
3911 last_xact_id = DEFAULT,
3913 pref_first_given_name = NULL,
3914 pref_second_given_name = NULL,
3915 pref_family_name = NULL,
3917 name_keywords = NULL,
3918 create_date = now(),
3923 $$ LANGUAGE plpgsql;
3925 SELECT evergreen.upgrade_deps_block_check('1305', :eg_version);
3927 CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
3931 picklist_row RECORD;
3936 -- Bail if src_usr equals dest_usr because the result of merging a
3937 -- user with itself is not what you want.
3938 IF src_usr = dest_usr THEN
3942 -- do some initial cleanup
3943 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
3944 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
3945 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
3949 DELETE FROM actor.card where usr = src_usr;
3951 IF deactivate_cards THEN
3952 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
3954 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
3959 DELETE FROM actor.usr_address WHERE usr = src_usr;
3961 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
3964 UPDATE actor.usr_message SET usr = dest_usr WHERE usr = src_usr;
3965 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
3966 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
3967 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
3968 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
3971 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
3972 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
3973 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
3974 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
3979 -- For each *_bucket table: transfer every bucket belonging to src_usr
3980 -- into the custody of dest_usr.
3982 -- In order to avoid colliding with an existing bucket owned by
3983 -- the destination user, append the source user's id (in parenthesese)
3984 -- to the name. If you still get a collision, add successive
3985 -- spaces to the name and keep trying until you succeed.
3989 FROM container.biblio_record_entry_bucket
3990 WHERE owner = src_usr
3992 suffix := ' (' || src_usr || ')';
3995 UPDATE container.biblio_record_entry_bucket
3996 SET owner = dest_usr, name = name || suffix
3997 WHERE id = bucket_row.id;
3998 EXCEPTION WHEN unique_violation THEN
3999 suffix := suffix || ' ';
4008 FROM container.call_number_bucket
4009 WHERE owner = src_usr
4011 suffix := ' (' || src_usr || ')';
4014 UPDATE container.call_number_bucket
4015 SET owner = dest_usr, name = name || suffix
4016 WHERE id = bucket_row.id;
4017 EXCEPTION WHEN unique_violation THEN
4018 suffix := suffix || ' ';
4027 FROM container.copy_bucket
4028 WHERE owner = src_usr
4030 suffix := ' (' || src_usr || ')';
4033 UPDATE container.copy_bucket
4034 SET owner = dest_usr, name = name || suffix
4035 WHERE id = bucket_row.id;
4036 EXCEPTION WHEN unique_violation THEN
4037 suffix := suffix || ' ';
4046 FROM container.user_bucket
4047 WHERE owner = src_usr
4049 suffix := ' (' || src_usr || ')';
4052 UPDATE container.user_bucket
4053 SET owner = dest_usr, name = name || suffix
4054 WHERE id = bucket_row.id;
4055 EXCEPTION WHEN unique_violation THEN
4056 suffix := suffix || ' ';
4063 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
4066 -- transfer queues the same way we transfer buckets (see above)
4070 WHERE owner = src_usr
4072 suffix := ' (' || src_usr || ')';
4075 UPDATE vandelay.queue
4076 SET owner = dest_usr, name = name || suffix
4077 WHERE id = queue_row.id;
4078 EXCEPTION WHEN unique_violation THEN
4079 suffix := suffix || ' ';
4086 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
4089 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
4090 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
4091 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
4092 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
4093 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
4096 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
4097 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
4098 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
4099 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
4101 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
4102 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
4103 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
4104 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
4106 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
4107 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
4108 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
4109 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
4110 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
4113 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
4114 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
4115 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
4117 -- transfer picklists the same way we transfer buckets (see above)
4121 WHERE owner = src_usr
4123 suffix := ' (' || src_usr || ')';
4127 SET owner = dest_usr, name = name || suffix
4128 WHERE id = picklist_row.id;
4129 EXCEPTION WHEN unique_violation THEN
4130 suffix := suffix || ' ';
4137 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
4138 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
4139 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
4140 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
4141 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
4142 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
4143 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
4144 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
4147 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
4148 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
4149 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
4150 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
4151 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
4152 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
4155 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
4156 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
4159 -- It's not uncommon to define the reporter schema in a replica
4160 -- DB only, so don't assume these tables exist in the write DB.
4162 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
4163 EXCEPTION WHEN undefined_table THEN
4167 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
4168 EXCEPTION WHEN undefined_table THEN
4172 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
4173 EXCEPTION WHEN undefined_table THEN
4177 -- transfer folders the same way we transfer buckets (see above)
4180 FROM reporter.template_folder
4181 WHERE owner = src_usr
4183 suffix := ' (' || src_usr || ')';
4186 UPDATE reporter.template_folder
4187 SET owner = dest_usr, name = name || suffix
4188 WHERE id = folder_row.id;
4189 EXCEPTION WHEN unique_violation THEN
4190 suffix := suffix || ' ';
4196 EXCEPTION WHEN undefined_table THEN
4200 -- transfer folders the same way we transfer buckets (see above)
4203 FROM reporter.report_folder
4204 WHERE owner = src_usr
4206 suffix := ' (' || src_usr || ')';
4209 UPDATE reporter.report_folder
4210 SET owner = dest_usr, name = name || suffix
4211 WHERE id = folder_row.id;
4212 EXCEPTION WHEN unique_violation THEN
4213 suffix := suffix || ' ';
4219 EXCEPTION WHEN undefined_table THEN
4223 -- transfer folders the same way we transfer buckets (see above)
4226 FROM reporter.output_folder
4227 WHERE owner = src_usr
4229 suffix := ' (' || src_usr || ')';
4232 UPDATE reporter.output_folder
4233 SET owner = dest_usr, name = name || suffix
4234 WHERE id = folder_row.id;
4235 EXCEPTION WHEN unique_violation THEN
4236 suffix := suffix || ' ';
4242 EXCEPTION WHEN undefined_table THEN
4246 -- propagate preferred name values from the source user to the
4247 -- destination user, but only when values are not being replaced.
4248 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
4249 UPDATE actor.usr SET
4251 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
4252 pref_first_given_name =
4253 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
4254 pref_second_given_name =
4255 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
4257 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
4259 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
4260 WHERE id = dest_usr;
4262 -- Copy and deduplicate name keywords
4263 -- String -> array -> rows -> DISTINCT -> array -> string
4264 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
4265 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
4266 UPDATE actor.usr SET name_keywords = (
4268 SELECT DISTINCT UNNEST(
4269 REGEXP_SPLIT_TO_ARRAY(
4270 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
4271 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
4274 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
4275 ) WHERE id = dest_usr;
4277 -- Finally, delete the source user
4278 PERFORM actor.usr_delete(src_usr,dest_usr);
4281 $$ LANGUAGE plpgsql;
4285 -- Update auditor tables to catch changes to source tables.
4286 -- Can be removed/skipped if there were no schema changes.
4287 SELECT auditor.update_auditors();