3.6.2 to 3.7-beta DB update script
[Evergreen.git] / Open-ILS / src / sql / Pg / version-upgrade / 3.6.2-3.7-beta-upgrade-db.sql
1 --Upgrade Script for 3.6.2 to 3.7-beta
2 \set eg_version '''3.7-beta'''
3 BEGIN;
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.7-beta', :eg_version);
5
6 SELECT evergreen.upgrade_deps_block_check('1247', :eg_version);
7
8 INSERT INTO permission.perm_list (id,code,description) VALUES (627,'SSO_ADMIN','Modify patron SSO settings');
9
10 INSERT INTO config.org_unit_setting_type
11 ( name, grp, label, description, datatype, update_perm )
12 VALUES
13 ('opac.login.shib_sso.enable',
14  'opac',
15  oils_i18n_gettext('opac.login.shib_sso.enable', 'Enable Shibboleth SSO for the OPAC', 'coust', 'label'),
16  oils_i18n_gettext('opac.login.shib_sso.enable', 'Enable Shibboleth SSO for the OPAC', 'coust', 'description'),
17  'bool', 627),
18 ('opac.login.shib_sso.entityId',
19  'opac',
20  oils_i18n_gettext('opac.login.shib_sso.entityId', 'Shibboleth SSO Entity ID', 'coust', 'label'),
21  oils_i18n_gettext('opac.login.shib_sso.entityId', 'Which configured Entity ID to use for SSO when there is more than one available to Shibboleth', 'coust', 'description'),
22  'string', 627),
23 ('opac.login.shib_sso.logout',
24  'opac',
25  oils_i18n_gettext('opac.login.shib_sso.logout', 'Log out of the Shibboleth IdP', 'coust', 'label'),
26  oils_i18n_gettext('opac.login.shib_sso.logout', 'When logging out of Evergreen, also force a logout of the IdP behind Shibboleth', 'coust', 'description'),
27  'bool', 627),
28 ('opac.login.shib_sso.allow_native',
29  'opac',
30  oils_i18n_gettext('opac.login.shib_sso.allow_native', 'Allow both Shibboleth and native OPAC authentication', 'coust', 'label'),
31  oils_i18n_gettext('opac.login.shib_sso.allow_native', 'When Shibboleth SSO is enabled, also allow native Evergreen authentication', 'coust', 'description'),
32  'bool', 627),
33 ('opac.login.shib_sso.evergreen_matchpoint',
34  'opac',
35  oils_i18n_gettext('opac.login.shib_sso.evergreen_matchpoint', 'Evergreen SSO matchpoint', 'coust', 'label'),
36  oils_i18n_gettext('opac.login.shib_sso.evergreen_matchpoint',
37   'Evergreen-side field to match a patron against for Shibboleth SSO. Default is usrname.  Other reasonable values would be barcode or email.',
38   'coust', 'description'),
39  'string', 627),
40 ('opac.login.shib_sso.shib_matchpoint',
41  'opac',
42  oils_i18n_gettext('opac.login.shib_sso.shib_matchpoint', 'Shibboleth SSO matchpoint', 'coust', 'label'),
43  oils_i18n_gettext('opac.login.shib_sso.shib_matchpoint',
44   'Shibboleth-side field to match a patron against for Shibboleth SSO. Default is uid; use eppn for Active Directory', 'coust', 'description'),
45  'string', 627)
46 ;
47
48
49 SELECT evergreen.upgrade_deps_block_check('1248', :eg_version);
50
51 DO LANGUAGE plpgsql $$
52 DECLARE
53   ind RECORD;
54   tablist TEXT;
55 BEGIN
56
57   -- We only want to mess with gist indexes in stock Evergreen.
58   -- If you've added your own convert them or don't as you see fit.
59   PERFORM
60   FROM pg_index idx
61     JOIN pg_class cls ON cls.oid=idx.indexrelid
62     JOIN pg_namespace sc ON sc.oid = cls.relnamespace
63     JOIN pg_class tab ON tab.oid=idx.indrelid
64     JOIN pg_attribute at ON (at.attnum = ANY(idx.indkey) AND at.attrelid = tab.oid)
65     JOIN pg_am am ON am.oid=cls.relam
66   WHERE am.amname = 'gist'
67     AND cls.relname IN (
68       'authority_full_rec_index_vector_idx',
69       'authority_simple_heading_index_vector_idx',
70       'metabib_identifier_field_entry_index_vector_idx',
71       'metabib_combined_identifier_field_entry_index_vector_idx',
72       'metabib_title_field_entry_index_vector_idx',
73       'metabib_combined_title_field_entry_index_vector_idx',
74       'metabib_author_field_entry_index_vector_idx',
75       'metabib_combined_author_field_entry_index_vector_idx',
76       'metabib_subject_field_entry_index_vector_idx',
77       'metabib_combined_subject_field_entry_index_vector_idx',
78       'metabib_keyword_field_entry_index_vector_idx',
79       'metabib_combined_keyword_field_entry_index_vector_idx',
80       'metabib_series_field_entry_index_vector_idx',
81       'metabib_combined_series_field_entry_index_vector_idx',
82       'metabib_full_rec_index_vector_idx'
83     );
84
85   IF NOT FOUND THEN
86     RETURN;
87   END IF;
88   
89   tablist := '';
90   
91   RAISE NOTICE 'Converting GIST indexes into GIN indexes...';
92
93   FOR ind IN SELECT sc.nspname AS sch, tab.relname AS tab, cls.relname AS idx, at.attname AS col
94              FROM pg_index idx
95                JOIN pg_class cls ON cls.oid=idx.indexrelid
96                JOIN pg_namespace sc ON sc.oid = cls.relnamespace
97                JOIN pg_class tab ON tab.oid=idx.indrelid
98                JOIN pg_attribute at ON (at.attnum = ANY(idx.indkey) AND at.attrelid = tab.oid)
99                JOIN pg_am am ON am.oid=cls.relam
100              WHERE am.amname = 'gist'
101                AND cls.relname IN (
102                  'authority_full_rec_index_vector_idx',
103                  'authority_simple_heading_index_vector_idx',
104                  'metabib_identifier_field_entry_index_vector_idx',
105                  'metabib_combined_identifier_field_entry_index_vector_idx',
106                  'metabib_title_field_entry_index_vector_idx',
107                  'metabib_combined_title_field_entry_index_vector_idx',
108                  'metabib_author_field_entry_index_vector_idx',
109                  'metabib_combined_author_field_entry_index_vector_idx',
110                  'metabib_subject_field_entry_index_vector_idx',
111                  'metabib_combined_subject_field_entry_index_vector_idx',
112                  'metabib_keyword_field_entry_index_vector_idx',
113                  'metabib_combined_keyword_field_entry_index_vector_idx',
114                  'metabib_series_field_entry_index_vector_idx',
115                  'metabib_combined_series_field_entry_index_vector_idx',
116                  'metabib_full_rec_index_vector_idx'
117                )
118   LOOP
119     -- Move existing index out of the way so there's no difference between new databases and upgraded databases
120     EXECUTE FORMAT('ALTER INDEX %I.%I RENAME TO %I_gist', ind.sch, ind.idx, ind.idx);
121
122     -- Meet the new index, same as the old index (almost)
123     EXECUTE FORMAT('CREATE INDEX %I ON %I.%I USING GIN (%I)', ind.idx, ind.sch, ind.tab, ind.col);
124
125     -- And drop the old index
126     EXECUTE FORMAT('DROP INDEX %I.%I_gist', ind.sch, ind.idx);
127
128     tablist := tablist || '           ' || ind.sch || '.' || ind.tab || E'\n';
129
130   END LOOP;
131
132   RAISE NOTICE E'Conversion Complete.\n\n           You should run a VACUUM ANALYZE on the following tables soon:\n%', tablist;
133
134 END $$;
135
136
137
138 SELECT evergreen.upgrade_deps_block_check('1249', :eg_version);
139
140 INSERT INTO config.usr_setting_type (
141     name,
142     opac_visible,
143     label,
144     description,
145     datatype,
146     reg_default
147 ) VALUES (
148     'circ.default_overdue_notices_enabled',
149     TRUE,
150     oils_i18n_gettext(
151         'circ.default_overdue_notices_enabled',
152         'Receive Overdue and Courtesy Emails',
153         'cust',
154         'label'
155     ),
156     oils_i18n_gettext(
157         'circ.default_overdue_notices_enabled',
158         'Receive overdue and predue email notifications',
159         'cust',
160         'description'
161     ),
162     'bool',
163     'true'
164 );
165
166
167 \qecho
168 \qecho The following query will set the circ.default_overdue_notices_enabled
169 \qecho user setting to true (the default value) for all existing users,
170 \qecho ensuring they continue to receive overdue/predue emails.
171 \qecho
172 \qecho     INSERT INTO actor.usr_setting (usr, name, value)
173 \qecho     SELECT
174 \qecho         id,
175 \qecho         'circ.default_overdue_notices_enabled',
176 \qecho         'true'
177 \qecho     FROM actor.usr;
178 \qecho
179 \qecho The following query will add the circ.default_overdue_notices_enabled
180 \qecho user setting as an opt-in setting for all action triggers that send
181 \qecho emails based on a circ being due (unless another opt-in setting is
182 \qecho already in use).
183 \qecho
184 \qecho     UPDATE action_trigger.event_definition
185 \qecho     SET opt_in_setting = 'circ.default_overdue_notices_enabled',
186 \qecho         usr_field = 'usr'
187 \qecho     WHERE opt_in_setting IS NULL
188 \qecho         AND hook = 'checkout.due'
189 \qecho         AND reactor = 'SendEmail';
190 \qecho
191 \qecho Evergreen admins who wish to use the new setting should run both of
192 \qecho the above queries.  Admins who do not wish to use it, or who are
193 \qecho already using a custom opt-in setting of their own, do not need to
194 \qecho do anything.
195 \qecho
196
197
198 SELECT evergreen.upgrade_deps_block_check('1250', :eg_version);
199
200 CREATE TABLE action.batch_hold_event (
201     id          SERIAL  PRIMARY KEY,
202     staff       INT     NOT NULL REFERENCES actor.usr (id) ON UPDATE CASCADE ON DELETE CASCADE,
203     bucket      INT     NOT NULL REFERENCES container.user_bucket (id) ON UPDATE CASCADE ON DELETE CASCADE,
204     target      INT     NOT NULL,
205     hold_type   TEXT    NOT NULL DEFAULT 'T', -- maybe different hold types in the future...
206     run_date    TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
207     cancelled   TIMESTAMP WITH TIME ZONE
208 );
209
210 CREATE TABLE action.batch_hold_event_map (
211     id                  SERIAL  PRIMARY KEY,
212     batch_hold_event    INT     NOT NULL REFERENCES action.batch_hold_event (id) ON UPDATE CASCADE ON DELETE CASCADE,
213     hold                INT     NOT NULL REFERENCES action.hold_request (id) ON UPDATE CASCADE ON DELETE CASCADE
214 );
215
216 INSERT INTO container.user_bucket_type (code,label) VALUES ('hold_subscription','Hold Group Container');
217
218 INSERT INTO config.org_unit_setting_type
219     (name, label, description, grp, datatype)
220 VALUES (
221     'holds.subscription.randomize',
222     oils_i18n_gettext(
223         'holds.subscription.randomize',
224         'Randomize group hold order',
225         'coust',
226         'label'
227     ),
228     oils_i18n_gettext(
229         'holds.subscription.randomize',
230         'When placing a batch group hold, randomize the order of the patrons receiving the holds so they are not always in the same order.',
231         'coust',
232         'description'
233     ),
234     'holds',
235     'bool'
236 );
237
238 INSERT INTO permission.perm_list (id,code,description)
239   VALUES ( 628, 'MANAGE_HOLD_GROUPS', oils_i18n_gettext(628, 'Manage hold groups and hold group events', 'ppl', 'description'));
240
241 INSERT INTO action.hold_request_cancel_cause (id,label)
242   VALUES ( 8, oils_i18n_gettext(8, 'Hold Group Event rollback', 'ahrcc', 'label'));
243
244 INSERT INTO action_trigger.event_definition (active, owner, name, hook, validator, reactor, delay, delay_field, group_field, cleanup_success, template)
245     VALUES ('f', 1, 'Hold Group Hold Placed for Patron Email Notification', 'hold_request.success', 'NOOP_True', 'SendEmail', '30 minutes', 'request_time', 'usr', 'CreateHoldNotification',
246 $$
247 [%- USE date -%]
248 [%- user = target.0.usr -%]
249 To: [%- params.recipient_email || user.email %]
250 From: [%- params.sender_email || default_sender %]
251 Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %]
252 Subject: Subcription Hold placed for you
253 Auto-Submitted: auto-generated
254
255 Dear [% user.family_name %], [% user.first_given_name %]
256 The following items have been placed on hold for you:
257
258 [% FOR hold IN target %]
259     [%- copy_details = helpers.get_copy_bib_basics(hold.current_copy.id) -%]
260     Title: [% copy_details.title %]
261     Author: [% copy_details.author %]
262     Call Number: [% hold.current_copy.call_number.label %]
263     Barcode: [% hold.current_copy.barcode %]
264     Library: [% hold.pickup_lib.name %]
265 [% END %]
266
267 $$);
268
269 INSERT INTO action_trigger.environment (event_def, path ) VALUES
270 ( currval('action_trigger.event_definition_id_seq'), 'usr' ),
271 ( currval('action_trigger.event_definition_id_seq'), 'pickup_lib' ),
272 ( currval('action_trigger.event_definition_id_seq'), 'current_copy.call_number' );
273
274
275 INSERT INTO action_trigger.event_definition (
276     active, owner, name, hook, validator, reactor, cleanup_success,
277     delay, delay_field, group_field, template
278 ) VALUES (
279     false, 1, 'Hold Group Hold Placed for Patron SMS Notification', 'hold_request.success', 'NOOP_True',
280     'SendSMS', 'CreateHoldNotification', '00:30:00', 'shelf_time', 'sms_notify',
281     '[%- USE date -%]
282 [%- user = target.0.usr -%]
283 From: [%- params.sender_email || default_sender %]
284 Date: [%- date.format(date.now, ''%a, %d %b %Y %T -0000'', gmt => 1) %]
285 To: [%- params.recipient_email || helpers.get_sms_gateway_email(target.0.sms_carrier,target.0.sms_notify) %]
286 Subject: [% target.size %] subscription hold(s) placed for you
287 Auto-Submitted: auto-generated
288
289 [% FOR hold IN target %][%-
290   bibxml = helpers.xml_doc( hold.current_copy.call_number.record.marc );
291   title = "";
292   FOR part IN bibxml.findnodes(''//*[@tag="245"]/*[@code="a"]'');
293     title = title _ part.textContent;
294   END;
295   author = bibxml.findnodes(''//*[@tag="100"]/*[@code="a"]'').textContent;
296 %][% hold.usr.first_given_name %]:[% title %] @ [% hold.pickup_lib.name %]
297 [% END %]
298 '
299 );
300
301 INSERT INTO action_trigger.environment (
302     event_def,
303     path
304 ) VALUES (
305     currval('action_trigger.event_definition_id_seq'),
306     'current_copy.call_number.record.simple_record'
307 ), (
308     currval('action_trigger.event_definition_id_seq'),
309     'usr'
310 ), (
311     currval('action_trigger.event_definition_id_seq'),
312     'pickup_lib.billing_address'
313 );
314
315 INSERT INTO action_trigger.event_params (event_def, param, value)
316     VALUES (currval('action_trigger.event_definition_id_seq'), 'check_sms_notify', 1);
317
318
319
320
321 SELECT evergreen.upgrade_deps_block_check('1251', :eg_version);
322
323 INSERT INTO config.org_unit_setting_type
324     (grp, name, datatype, label, description)
325 VALUES (
326     'circ',
327     'circ.renew.expired_patron_allow', 'bool',
328     oils_i18n_gettext(
329         'circ.renew.expired_patron_allow',
330         'Allow renewal request if renewal recipient privileges have expired',
331         'coust',
332         'label'
333     ),
334     oils_i18n_gettext(
335         'circ.renew.expired_patron_allow',
336         'If enabled, users within the org unit who are expired may still renew items.',
337         'coust',
338         'description'
339     )
340 );
341
342
343 SELECT evergreen.upgrade_deps_block_check('1252', :eg_version);
344
345 INSERT INTO config.coded_value_map
346     (id, ctype, code, opac_visible, value, search_label)
347     SELECT 1738,'search_format','video', true,
348     oils_i18n_gettext(1738, 'All Videos', 'ccvm', 'value'),
349     oils_i18n_gettext(1738, 'All Videos', 'ccvm', 'search_label')
350     WHERE NOT EXISTS (
351         SELECT 1 FROM config.coded_value_map WHERE id=1738
352         OR value = 'All Videos' OR search_label = 'All Videos'
353     );
354
355 INSERT INTO config.composite_attr_entry_definition (coded_value, definition)
356     SELECT 1738, '{"_attr":"item_type","_val":"g"}'
357 WHERE NOT EXISTS (
358     SELECT 1 FROM config.composite_attr_entry_definition WHERE coded_value = 1738
359 );
360
361
362 SELECT evergreen.upgrade_deps_block_check('1253', :eg_version);
363
364 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
365 VALUES (
366     'eg.print.template_context.booking_capture', 'gui', 'string',
367     oils_i18n_gettext(
368         'eg.print.template_context.booking_capture',
369         'Print Template Context: booking_capture',
370         'cwst', 'label'
371     )
372 );
373
374
375 SELECT evergreen.upgrade_deps_block_check('1254', :eg_version);
376
377 -- XXX Committer: confirm ID below is the next available!
378 INSERT INTO permission.perm_list (id, code, description)
379     VALUES ( 629, 'ADMIN_LIBRARY_GROUPS', 'Administer library groups');
380
381 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
382 VALUES (
383     'eg.grid.admin.server.actor.org_lasso', 'gui', 'object',
384     oils_i18n_gettext(
385         'eg.grid.admin.server.actor.org_lasso',
386         'Grid Config: admin.server.actor.org_lasso',
387         'cwst', 'label'
388     )
389 ), (
390     'eg.grid.admin.server.actor.org_lasso_map', 'gui', 'object',
391     oils_i18n_gettext(
392         'eg.grid.admin.server.actor.org_lasso_map',
393         'Grid Config: admin.server.actor.org_lasso_map',
394         'cwst', 'label'
395     )
396 );
397
398 ALTER TABLE actor.org_lasso ADD COLUMN global BOOL NOT NULL DEFAULT FALSE;
399
400
401
402 -- check whether patch can be applied
403 SELECT evergreen.upgrade_deps_block_check('1255', :eg_version);
404
405 CREATE EXTENSION earthdistance CASCADE;
406
407 -- 005.schema.actors.sql
408
409 -- CREATE TABLE actor.org_address (
410 --     ...
411 --     latitude    FLOAT,
412 --     longitude   FLOAT
413 -- );
414
415 ALTER TABLE actor.org_address ADD COLUMN latitude FLOAT;
416 ALTER TABLE actor.org_address ADD COLUMN longitude FLOAT;
417
418 -- 002.schema.config.sql
419
420 CREATE TABLE config.geolocation_service (
421     id           SERIAL PRIMARY KEY,
422     active       BOOLEAN,
423     owner        INT NOT NULL, -- REFERENCES actor.org_unit (id)
424     name         TEXT,
425     service_code TEXT,
426     api_key      TEXT
427 );
428
429 -- 800.fkeys.sql
430
431 ALTER TABLE config.geolocation_service ADD CONSTRAINT cgs_owner_fkey
432     FOREIGN KEY (owner) REFERENCES  actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED;
433
434 -- 950.data.seed-values.sql
435
436 INSERT INTO config.global_flag (name, value, enabled, label)
437 VALUES (
438     'opac.use_geolocation',
439     NULL,
440     FALSE,
441     oils_i18n_gettext(
442         'opac.use_geolocation',
443         'Offer use of geographic location services in the public catalog',
444         'cgf', 'label'
445     )
446 );
447
448 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype)
449 VALUES (
450     'opac.holdings_sort_by_geographic_proximity',
451     oils_i18n_gettext('opac.holdings_sort_by_geographic_proximity',
452         'Enable Holdings Sort by Geographic Proximity',
453         'coust', 'label'),
454     'opac',
455     oils_i18n_gettext('opac.holdings_sort_by_geographic_proximity',
456         'When set to TRUE, will cause the record details page to display the controls for sorting holdings by geographic proximity. This also depends on the global flag opac.use_geolocation being enabled.',
457         'coust', 'description'),
458     'bool'
459 );
460
461 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype)
462 VALUES (
463     'opac.geographic_proximity_in_miles',
464     oils_i18n_gettext('opac.geographic_proximity_in_miles',
465         'Show Geographic Proximity in Miles',
466         'coust', 'label'),
467     'opac',
468     oils_i18n_gettext('opac.geographic_proximity_in_miles',
469         'When set to TRUE, will cause the record details page to show distances for geographic proximity in miles instead of kilometers.',
470         'coust', 'description'),
471     'bool'
472 );
473
474 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype, fm_class)
475 VALUES (
476     'opac.geographic_location_service_for_address',
477     oils_i18n_gettext('opac.geographic_location_service_for_address',
478         'Geographic Location Service to use for Addresses',
479         'coust', 'label'),
480     'opac',
481     oils_i18n_gettext('opac.geographic_location_service_for_address',
482         'Specifies which geographic location service to use for converting address input to geographic coordinates.',
483         'coust', 'description'),
484     'link', 'cgs'
485 );
486
487 INSERT INTO permission.perm_list ( id, code, description ) VALUES
488  ( 630, 'VIEW_GEOLOCATION_SERVICES', oils_i18n_gettext(630,
489     'View geographic location services', 'ppl', 'description')),
490  ( 631, 'ADMIN_GEOLOCATION_SERVICES', oils_i18n_gettext(631,
491     'Administer geographic location services', 'ppl', 'description'))
492 ;
493
494 -- geolocation-aware variant
495 CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT, plat FLOAT, plon FLOAT)
496 RETURNS INTEGER AS $$
497     SELECT COALESCE(
498
499         -- lib matches search_lib
500         (SELECT CASE WHEN $1 = $2 THEN -20000 END),
501
502         -- lib matches pref_lib
503         (SELECT CASE WHEN $1 = $3 THEN -10000 END),
504
505
506         -- pref_lib is a child of search_lib and lib is a child of pref lib.
507         -- For example, searching CONS, pref lib is SYS1,
508         -- copies at BR1 and BR2 sort to the front.
509         (SELECT distance - 5000
510             FROM actor.org_unit_descendants_distance($3)
511             WHERE id = $1 AND $3 IN (
512                 SELECT id FROM actor.org_unit_descendants($2))),
513
514         -- lib is a child of search_lib
515         (SELECT distance FROM actor.org_unit_descendants_distance($2) WHERE id = $1),
516
517         -- all others pay cash
518         1000
519     ) + ((SELECT CASE WHEN addr.latitude IS NULL THEN 0 ELSE -20038 END) + (earth_distance( -- shortest GC distance is returned, only half the circumfrence is needed
520             ll_to_earth(
521                 COALESCE(addr.latitude,plat), -- if the org has no coords, we just
522                 COALESCE(addr.longitude,plon) -- force 0 distance and let the above tie-break
523             ),ll_to_earth(plat,plon)
524         ) / 1000)::INT ) -- earth_distance is in meters, convert to kilometers and subtract from largest distance
525     FROM actor.org_unit org
526          LEFT JOIN actor.org_address addr ON (org.billing_address = addr.id)
527     WHERE org.id = $1;
528 $$ LANGUAGE SQL STABLE;
529
530
531 SELECT evergreen.upgrade_deps_block_check('1256', :eg_version);
532
533 CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
534 CREATE EXTENSION IF NOT EXISTS pg_trgm;
535
536 INSERT INTO config.internal_flag (name, value, enabled) VALUES ('symspell.prefix_length', '6', TRUE);
537 INSERT INTO config.internal_flag (name, value, enabled) VALUES ('symspell.max_edit_distance', '3', TRUE);
538
539 INSERT into config.org_unit_setting_type
540 ( name, grp, label, description, datatype )
541 VALUES
542 ( 'opac.did_you_mean.max_suggestions', 'opac',
543    oils_i18n_gettext(
544      'opac.did_you_mean.max_suggestions',
545      'Maximum number of spelling suggestions that may be offered',
546      'coust', 'label'),
547    oils_i18n_gettext(
548      'opac.did_you_mean.max_suggestions',
549      '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.',
550      'coust', 'description'),
551    'integer' );
552
553 INSERT into config.org_unit_setting_type
554 ( name, grp, label, description, datatype )
555 VALUES
556 ( 'opac.did_you_mean.low_result_threshold', 'opac',
557    oils_i18n_gettext(
558      'opac.did_you_mean.low_result_threshold',
559      'Maximum search result count at which spelling suggestions may be offered',
560      'coust', 'label'),
561    oils_i18n_gettext(
562      'opac.did_you_mean.low_result_threshold',
563      'If a search results in this number or fewer results, and there are correctable spelling mistakes, a suggested search may be provided.',
564      'coust', 'description'),
565    'integer' );
566
567 INSERT into config.org_unit_setting_type
568 ( name, grp, label, description, datatype )
569 VALUES
570 ( 'search.symspell.min_suggestion_use_threshold', 'opac',
571    oils_i18n_gettext(
572      'search.symspell.min_suggestion_use_threshold',
573      'Minimum required uses of a spelling suggestions that may be offered',
574      'coust', 'label'),
575    oils_i18n_gettext(
576      'search.symspell.min_suggestion_use_threshold',
577      'The number of bibliographic records (more or less) that a spelling suggestion must appear in to be considered before offering it to a user. Defaults to 1 (must appear in the bib data).',
578      'coust', 'description'),
579    'integer' );
580
581 INSERT into config.org_unit_setting_type
582 ( name, grp, label, description, datatype )
583 VALUES
584 ( 'search.symspell.soundex.weight', 'opac',
585    oils_i18n_gettext(
586      'search.symspell.soundex.weight',
587      'Soundex score weighting in OPAC spelling suggestions.',
588      'coust', 'label'),
589    oils_i18n_gettext(
590      'search.symspell.soundex.weight',
591      'Soundex, trgm, and keyboard distance similarity measures can be combined to form a secondary ordering parameter for spelling suggestions. This controls the relative weight of the scaled soundex component. Defaults to 0 for "off".',
592      'coust', 'description'),
593    'integer' );
594
595 INSERT into config.org_unit_setting_type
596 ( name, grp, label, description, datatype )
597 VALUES
598 ( 'search.symspell.pg_trgm.weight', 'opac',
599    oils_i18n_gettext(
600      'search.symspell.pg_trgm.weight',
601      'Pg_trgm score weighting in OPAC spelling suggestions.',
602      'coust', 'label'),
603    oils_i18n_gettext(
604      'search.symspell.pg_trgm.weight',
605      'Soundex, pg_trgm, and keyboard distance similarity measures can be combined to form a secondary ordering parameter for spelling suggestions. This controls the relative weight of the scaled pg_trgm component. Defaults to 0 for "off".',
606      'coust', 'description'),
607    'integer' );
608
609 INSERT into config.org_unit_setting_type
610 ( name, grp, label, description, datatype )
611 VALUES
612 ( 'search.symspell.keyboard_distance.weight', 'opac',
613    oils_i18n_gettext(
614      'search.symspell.keyboard_distance.weight',
615      'Keyboard distance score weighting in OPAC spelling suggestions.',
616      'coust', 'label'),
617    oils_i18n_gettext(
618      'search.symspell.keyboard_distance.weight',
619      'Soundex, trgm, and keyboard distance similarity measures can be combined to form a secondary ordering parameter for spelling suggestions. This controls the relative weight of the scaled keyboard distance component. Defaults to 0 for "off".',
620      'coust', 'description'),
621    'integer' );
622
623 CREATE OR REPLACE FUNCTION evergreen.uppercase( TEXT ) RETURNS TEXT AS $$
624     return uc(shift);
625 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
626
627 CREATE OR REPLACE FUNCTION evergreen.text_array_merge_unique (
628     TEXT[], TEXT[]
629 ) RETURNS TEXT[] AS $F$
630     SELECT NULLIF(ARRAY(
631         SELECT * FROM UNNEST($1) x WHERE x IS NOT NULL
632             UNION
633         SELECT * FROM UNNEST($2) y WHERE y IS NOT NULL
634     ),'{}');
635 $F$ LANGUAGE SQL;
636
637 CREATE OR REPLACE FUNCTION evergreen.qwerty_keyboard_distance ( a TEXT, b TEXT ) RETURNS NUMERIC AS $F$
638 use String::KeyboardDistance qw(:all);
639 return qwerty_keyboard_distance(@_);
640 $F$ LANGUAGE PLPERLU STRICT IMMUTABLE;
641
642 CREATE OR REPLACE FUNCTION evergreen.qwerty_keyboard_distance_match ( a TEXT, b TEXT ) RETURNS NUMERIC AS $F$
643 use String::KeyboardDistance qw(:all);
644 return qwerty_keyboard_distance_match(@_);
645 $F$ LANGUAGE PLPERLU STRICT IMMUTABLE;
646
647 CREATE OR REPLACE FUNCTION evergreen.levenshtein_damerau_edistance ( a TEXT, b TEXT, INT ) RETURNS NUMERIC AS $F$
648 use Text::Levenshtein::Damerau::XS qw/xs_edistance/;
649 return xs_edistance(@_);
650 $F$ LANGUAGE PLPERLU STRICT IMMUTABLE;
651
652 CREATE TABLE search.symspell_dictionary (
653     keyword_count           INT     NOT NULL DEFAULT 0,
654     title_count             INT     NOT NULL DEFAULT 0,
655     author_count            INT     NOT NULL DEFAULT 0,
656     subject_count           INT     NOT NULL DEFAULT 0,
657     series_count            INT     NOT NULL DEFAULT 0,
658     identifier_count        INT     NOT NULL DEFAULT 0,
659
660     prefix_key              TEXT    PRIMARY KEY,
661
662     keyword_suggestions     TEXT[],
663     title_suggestions       TEXT[],
664     author_suggestions      TEXT[],
665     subject_suggestions     TEXT[],
666     series_suggestions      TEXT[],
667     identifier_suggestions  TEXT[]
668 ) WITH (fillfactor = 80);
669
670 CREATE OR REPLACE FUNCTION search.symspell_parse_words ( phrase TEXT )
671 RETURNS SETOF TEXT AS $F$
672     SELECT UNNEST(x) FROM regexp_matches($1, '([[:alnum:]]+''*[[:alnum:]]*)', 'g') x;
673 $F$ LANGUAGE SQL STRICT IMMUTABLE;
674
675 -- This version does not preserve input word order!
676 CREATE OR REPLACE FUNCTION search.symspell_parse_words_distinct ( phrase TEXT )
677 RETURNS SETOF TEXT AS $F$
678     SELECT DISTINCT UNNEST(x) FROM regexp_matches($1, '([[:alnum:]]+''*[[:alnum:]]*)', 'g') x;
679 $F$ LANGUAGE SQL STRICT IMMUTABLE;
680
681 CREATE OR REPLACE FUNCTION search.symspell_transfer_casing ( withCase TEXT, withoutCase TEXT )
682 RETURNS TEXT AS $F$
683 DECLARE
684     woChars TEXT[];
685     curr    TEXT;
686     ind     INT := 1;
687 BEGIN
688     woChars := regexp_split_to_array(withoutCase,'');
689     FOR curr IN SELECT x FROM regexp_split_to_table(withCase, '') x LOOP
690         IF curr = evergreen.uppercase(curr) THEN
691             woChars[ind] := evergreen.uppercase(woChars[ind]);
692         END IF;
693         ind := ind + 1;
694     END LOOP;
695     RETURN ARRAY_TO_STRING(woChars,'');
696 END;
697 $F$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
698
699 CREATE OR REPLACE FUNCTION search.symspell_generate_edits (
700     raw_word    TEXT,
701     dist        INT DEFAULT 1,
702     maxED       INT DEFAULT 3
703 ) RETURNS TEXT[] AS $F$
704 DECLARE
705     item    TEXT;
706     list    TEXT[] := '{}';
707     sublist TEXT[] := '{}';
708 BEGIN
709     FOR I IN 1 .. CHARACTER_LENGTH(raw_word) LOOP
710         item := SUBSTRING(raw_word FROM 1 FOR I - 1) || SUBSTRING(raw_word FROM I + 1);
711         IF NOT list @> ARRAY[item] THEN
712             list := item || list;
713             IF dist < maxED AND CHARACTER_LENGTH(raw_word) > dist + 1 THEN
714                 sublist := search.symspell_generate_edits(item, dist + 1, maxED) || sublist;
715             END IF;
716         END IF;
717     END LOOP;
718
719     IF dist = 1 THEN
720         RETURN evergreen.text_array_merge_unique(list, sublist);
721     ELSE
722         RETURN list || sublist;
723     END IF;
724 END;
725 $F$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
726
727 -- DROP TYPE search.symspell_lookup_output CASCADE;
728 CREATE TYPE search.symspell_lookup_output AS (
729     suggestion          TEXT,
730     suggestion_count    INT,
731     lev_distance        INT,
732     pg_trgm_sim         NUMERIC,
733     qwerty_kb_match     NUMERIC,
734     soundex_sim         NUMERIC,
735     input               TEXT,
736     norm_input          TEXT,
737     prefix_key          TEXT,
738     prefix_key_count    INT,
739     word_pos            INT
740 );
741
742 CREATE OR REPLACE FUNCTION search.symspell_lookup (
743     raw_input       TEXT,
744     search_class    TEXT,
745     verbosity       INT DEFAULT 2,
746     xfer_case       BOOL DEFAULT FALSE,
747     count_threshold INT DEFAULT 1,
748     soundex_weight  INT DEFAULT 0,
749     pg_trgm_weight  INT DEFAULT 0,
750     kbdist_weight   INT DEFAULT 0
751 ) RETURNS SETOF search.symspell_lookup_output AS $F$
752 DECLARE
753     prefix_length INT;
754     maxED         INT;
755     word_list   TEXT[];
756     edit_list   TEXT[] := '{}';
757     seen_list   TEXT[] := '{}';
758     output      search.symspell_lookup_output;
759     output_list search.symspell_lookup_output[];
760     entry       RECORD;
761     entry_key   TEXT;
762     prefix_key  TEXT;
763     sugg        TEXT;
764     input       TEXT;
765     word        TEXT;
766     w_pos       INT := -1;
767     smallest_ed INT := -1;
768     global_ed   INT;
769 BEGIN
770     SELECT value::INT INTO prefix_length FROM config.internal_flag WHERE name = 'symspell.prefix_length' AND enabled;
771     prefix_length := COALESCE(prefix_length, 6);
772
773     SELECT value::INT INTO maxED FROM config.internal_flag WHERE name = 'symspell.max_edit_distance' AND enabled;
774     maxED := COALESCE(maxED, 3);
775
776     word_list := ARRAY_AGG(x) FROM search.symspell_parse_words(raw_input) x;
777
778     -- Common case exact match test for preformance
779     IF verbosity = 0 AND CARDINALITY(word_list) = 1 AND CHARACTER_LENGTH(word_list[1]) <= prefix_length THEN
780         EXECUTE
781           'SELECT  '||search_class||'_suggestions AS suggestions,
782                    '||search_class||'_count AS count,
783                    prefix_key
784              FROM  search.symspell_dictionary
785              WHERE prefix_key = $1
786                    AND '||search_class||'_count >= $2 
787                    AND '||search_class||'_suggestions @> ARRAY[$1]' 
788           INTO entry USING evergreen.lowercase(word_list[1]), COALESCE(count_threshold,1);
789         IF entry.prefix_key IS NOT NULL THEN
790             output.lev_distance := 0; -- definitionally
791             output.prefix_key := entry.prefix_key;
792             output.prefix_key_count := entry.count;
793             output.suggestion_count := entry.count;
794             output.input := word_list[1];
795             IF xfer_case THEN
796                 output.suggestion := search.symspell_transfer_casing(output.input, entry.prefix_key);
797             ELSE
798                 output.suggestion := entry.prefix_key;
799             END IF;
800             output.norm_input := entry.prefix_key;
801             output.qwerty_kb_match := 1;
802             output.pg_trgm_sim := 1;
803             output.soundex_sim := 1;
804             RETURN NEXT output;
805             RETURN;
806         END IF;
807     END IF;
808
809     <<word_loop>>
810     FOREACH word IN ARRAY word_list LOOP
811         w_pos := w_pos + 1;
812         input := evergreen.lowercase(word);
813
814         IF CHARACTER_LENGTH(input) > prefix_length THEN
815             prefix_key := SUBSTRING(input FROM 1 FOR prefix_length);
816             edit_list := ARRAY[input,prefix_key] || search.symspell_generate_edits(prefix_key, 1, maxED);
817         ELSE
818             edit_list := input || search.symspell_generate_edits(input, 1, maxED);
819         END IF;
820
821         SELECT ARRAY_AGG(x ORDER BY CHARACTER_LENGTH(x) DESC) INTO edit_list FROM UNNEST(edit_list) x;
822
823         output_list := '{}';
824         seen_list := '{}';
825         global_ed := NULL;
826
827         <<entry_key_loop>>
828         FOREACH entry_key IN ARRAY edit_list LOOP
829             smallest_ed := -1;
830             IF global_ed IS NOT NULL THEN
831                 smallest_ed := global_ed;
832             END IF;
833             FOR entry IN EXECUTE
834                 'SELECT  '||search_class||'_suggestions AS suggestions,
835                          '||search_class||'_count AS count,
836                          prefix_key
837                    FROM  search.symspell_dictionary
838                    WHERE prefix_key = $1
839                          AND '||search_class||'_suggestions IS NOT NULL' 
840                 USING entry_key
841             LOOP
842                 FOREACH sugg IN ARRAY entry.suggestions LOOP
843                     IF NOT seen_list @> ARRAY[sugg] THEN
844                         seen_list := seen_list || sugg;
845                         IF input = sugg THEN -- exact match, no need to spend time on a call
846                             output.lev_distance := 0;
847                             output.suggestion_count = entry.count;
848                         ELSIF ABS(CHARACTER_LENGTH(input) - CHARACTER_LENGTH(sugg)) > maxED THEN
849                             -- They are definitionally too different to consider, just move on.
850                             CONTINUE;
851                         ELSE
852                             --output.lev_distance := levenshtein_less_equal(
853                             output.lev_distance := evergreen.levenshtein_damerau_edistance(
854                                 input,
855                                 sugg,
856                                 maxED
857                             );
858                             IF output.lev_distance < 0 THEN
859                                 -- The Perl module returns -1 for "more distant than max".
860                                 output.lev_distance := maxED + 1;
861                                 -- This short-circuit's the count test below for speed, bypassing
862                                 -- a couple useless tests.
863                                 output.suggestion_count := -1;
864                             ELSE
865                                 EXECUTE 'SELECT '||search_class||'_count FROM search.symspell_dictionary WHERE prefix_key = $1'
866                                     INTO output.suggestion_count USING sugg;
867                             END IF;
868                         END IF;
869
870                         -- The caller passes a minimum suggestion count threshold (or uses
871                         -- the default of 0) and if the suggestion has that many or less uses
872                         -- then we move on to the next suggestion, since this one is too rare.
873                         CONTINUE WHEN output.suggestion_count < COALESCE(count_threshold,1);
874
875                         -- Track the smallest edit distance among suggestions from this prefix key.
876                         IF smallest_ed = -1 OR output.lev_distance < smallest_ed THEN
877                             smallest_ed := output.lev_distance;
878                         END IF;
879
880                         -- Track the smallest edit distance for all prefix keys for this word.
881                         IF global_ed IS NULL OR smallest_ed < global_ed THEN
882                             global_ed = smallest_ed;
883                         END IF;
884
885                         -- Only proceed if the edit distance is <= the max for the dictionary.
886                         IF output.lev_distance <= maxED THEN
887                             IF output.lev_distance > global_ed AND verbosity <= 1 THEN
888                                 -- Lev distance is our main similarity measure. While
889                                 -- trgm or soundex similarity could be the main filter,
890                                 -- Lev is both language agnostic and faster.
891                                 --
892                                 -- Here we will skip suggestions that have a longer edit distance
893                                 -- than the shortest we've already found. This is simply an
894                                 -- optimization that allows us to avoid further processing
895                                 -- of this entry. It would be filtered out later.
896
897                                 CONTINUE;
898                             END IF;
899
900                             -- If we have an exact match on the suggestion key we can also avoid
901                             -- some function calls.
902                             IF output.lev_distance = 0 THEN
903                                 output.qwerty_kb_match := 1;
904                                 output.pg_trgm_sim := 1;
905                                 output.soundex_sim := 1;
906                             ELSE
907                                 output.qwerty_kb_match := evergreen.qwerty_keyboard_distance_match(input, sugg);
908                                 output.pg_trgm_sim := similarity(input, sugg);
909                                 output.soundex_sim := difference(input, sugg) / 4.0;
910                             END IF;
911
912                             -- Fill in some fields
913                             IF xfer_case THEN
914                                 output.suggestion := search.symspell_transfer_casing(word, sugg);
915                             ELSE
916                                 output.suggestion := sugg;
917                             END IF;
918                             output.prefix_key := entry.prefix_key;
919                             output.prefix_key_count := entry.count;
920                             output.input := word;
921                             output.norm_input := input;
922                             output.word_pos := w_pos;
923
924                             -- We can't "cache" a set of generated records directly, so
925                             -- here we build up an array of search.symspell_lookup_output
926                             -- records that we can revivicate later as a table using UNNEST().
927                             output_list := output_list || output;
928
929                             EXIT entry_key_loop WHEN smallest_ed = 0 AND verbosity = 0; -- exact match early exit
930                             CONTINUE entry_key_loop WHEN smallest_ed = 0 AND verbosity = 1; -- exact match early jump to the next key
931                         END IF; -- maxED test
932                     END IF; -- suggestion not seen test
933                 END LOOP; -- loop over suggestions
934             END LOOP; -- loop over entries
935         END LOOP; -- loop over entry_keys
936
937         -- Now we're done examining this word
938         IF verbosity = 0 THEN
939             -- Return the "best" suggestion from the smallest edit
940             -- distance group.  We define best based on the weighting
941             -- of the non-lev similarity measures and use the suggestion
942             -- use count to break ties.
943             RETURN QUERY
944                 SELECT * FROM UNNEST(output_list)
945                     ORDER BY lev_distance,
946                         (soundex_sim * COALESCE(soundex_weight,0))
947                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
948                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
949                         suggestion_count DESC
950                         LIMIT 1;
951         ELSIF verbosity = 1 THEN
952             -- Return all suggestions from the smallest
953             -- edit distance group.
954             RETURN QUERY
955                 SELECT * FROM UNNEST(output_list) WHERE lev_distance = smallest_ed
956                     ORDER BY (soundex_sim * COALESCE(soundex_weight,0))
957                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
958                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
959                         suggestion_count DESC;
960         ELSIF verbosity = 2 THEN
961             -- Return everything we find, along with relevant stats
962             RETURN QUERY
963                 SELECT * FROM UNNEST(output_list)
964                     ORDER BY lev_distance,
965                         (soundex_sim * COALESCE(soundex_weight,0))
966                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
967                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
968                         suggestion_count DESC;
969         ELSIF verbosity = 3 THEN
970             -- Return everything we find from the two smallest edit distance groups
971             RETURN QUERY
972                 SELECT * FROM UNNEST(output_list)
973                     WHERE lev_distance IN (SELECT DISTINCT lev_distance FROM UNNEST(output_list) ORDER BY 1 LIMIT 2)
974                     ORDER BY lev_distance,
975                         (soundex_sim * COALESCE(soundex_weight,0))
976                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
977                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
978                         suggestion_count DESC;
979         ELSIF verbosity = 4 THEN
980             -- Return everything we find from the two smallest edit distance groups that are NOT 0 distance
981             RETURN QUERY
982                 SELECT * FROM UNNEST(output_list)
983                     WHERE lev_distance IN (SELECT DISTINCT lev_distance FROM UNNEST(output_list) WHERE lev_distance > 0 ORDER BY 1 LIMIT 2)
984                     ORDER BY lev_distance,
985                         (soundex_sim * COALESCE(soundex_weight,0))
986                             + (pg_trgm_sim * COALESCE(pg_trgm_weight,0))
987                             + (qwerty_kb_match * COALESCE(kbdist_weight,0)) DESC,
988                         suggestion_count DESC;
989         END IF;
990     END LOOP; -- loop over words
991 END;
992 $F$ LANGUAGE PLPGSQL;
993
994 CREATE OR REPLACE FUNCTION search.symspell_build_raw_entry (
995     raw_input       TEXT,
996     source_class    TEXT,
997     no_limit        BOOL DEFAULT FALSE,
998     prefix_length   INT DEFAULT 6,
999     maxED           INT DEFAULT 3
1000 ) RETURNS SETOF search.symspell_dictionary AS $F$
1001 DECLARE
1002     key         TEXT;
1003     del_key     TEXT;
1004     key_list    TEXT[];
1005     entry       search.symspell_dictionary%ROWTYPE;
1006 BEGIN
1007     key := raw_input;
1008
1009     IF NOT no_limit AND CHARACTER_LENGTH(raw_input) > prefix_length THEN
1010         key := SUBSTRING(key FROM 1 FOR prefix_length);
1011         key_list := ARRAY[raw_input, key];
1012     ELSE
1013         key_list := ARRAY[key];
1014     END IF;
1015
1016     FOREACH del_key IN ARRAY key_list LOOP
1017         entry.prefix_key := del_key;
1018
1019         entry.keyword_count := 0;
1020         entry.title_count := 0;
1021         entry.author_count := 0;
1022         entry.subject_count := 0;
1023         entry.series_count := 0;
1024         entry.identifier_count := 0;
1025
1026         entry.keyword_suggestions := '{}';
1027         entry.title_suggestions := '{}';
1028         entry.author_suggestions := '{}';
1029         entry.subject_suggestions := '{}';
1030         entry.series_suggestions := '{}';
1031         entry.identifier_suggestions := '{}';
1032
1033         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
1034         IF source_class = 'title' THEN entry.title_suggestions := ARRAY[raw_input]; END IF;
1035         IF source_class = 'author' THEN entry.author_suggestions := ARRAY[raw_input]; END IF;
1036         IF source_class = 'subject' THEN entry.subject_suggestions := ARRAY[raw_input]; END IF;
1037         IF source_class = 'series' THEN entry.series_suggestions := ARRAY[raw_input]; END IF;
1038         IF source_class = 'identifier' THEN entry.identifier_suggestions := ARRAY[raw_input]; END IF;
1039         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
1040
1041         IF del_key = raw_input THEN
1042             IF source_class = 'keyword' THEN entry.keyword_count := 1; END IF;
1043             IF source_class = 'title' THEN entry.title_count := 1; END IF;
1044             IF source_class = 'author' THEN entry.author_count := 1; END IF;
1045             IF source_class = 'subject' THEN entry.subject_count := 1; END IF;
1046             IF source_class = 'series' THEN entry.series_count := 1; END IF;
1047             IF source_class = 'identifier' THEN entry.identifier_count := 1; END IF;
1048         END IF;
1049
1050         RETURN NEXT entry;
1051     END LOOP;
1052
1053     FOR del_key IN SELECT x FROM UNNEST(search.symspell_generate_edits(key, 1, maxED)) x LOOP
1054
1055         entry.keyword_suggestions := '{}';
1056         entry.title_suggestions := '{}';
1057         entry.author_suggestions := '{}';
1058         entry.subject_suggestions := '{}';
1059         entry.series_suggestions := '{}';
1060         entry.identifier_suggestions := '{}';
1061
1062         IF source_class = 'keyword' THEN entry.keyword_count := 0; END IF;
1063         IF source_class = 'title' THEN entry.title_count := 0; END IF;
1064         IF source_class = 'author' THEN entry.author_count := 0; END IF;
1065         IF source_class = 'subject' THEN entry.subject_count := 0; END IF;
1066         IF source_class = 'series' THEN entry.series_count := 0; END IF;
1067         IF source_class = 'identifier' THEN entry.identifier_count := 0; END IF;
1068
1069         entry.prefix_key := del_key;
1070
1071         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
1072         IF source_class = 'title' THEN entry.title_suggestions := ARRAY[raw_input]; END IF;
1073         IF source_class = 'author' THEN entry.author_suggestions := ARRAY[raw_input]; END IF;
1074         IF source_class = 'subject' THEN entry.subject_suggestions := ARRAY[raw_input]; END IF;
1075         IF source_class = 'series' THEN entry.series_suggestions := ARRAY[raw_input]; END IF;
1076         IF source_class = 'identifier' THEN entry.identifier_suggestions := ARRAY[raw_input]; END IF;
1077         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
1078
1079         RETURN NEXT entry;
1080     END LOOP;
1081
1082 END;
1083 $F$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1084
1085 CREATE OR REPLACE FUNCTION search.symspell_build_entries (
1086     full_input      TEXT,
1087     source_class    TEXT,
1088     old_input       TEXT DEFAULT NULL,
1089     include_phrases BOOL DEFAULT FALSE
1090 ) RETURNS SETOF search.symspell_dictionary AS $F$
1091 DECLARE
1092     prefix_length   INT;
1093     maxED           INT;
1094     word_list   TEXT[];
1095     input       TEXT;
1096     word        TEXT;
1097     entry       search.symspell_dictionary;
1098 BEGIN
1099     IF full_input IS NOT NULL THEN
1100         SELECT value::INT INTO prefix_length FROM config.internal_flag WHERE name = 'symspell.prefix_length' AND enabled;
1101         prefix_length := COALESCE(prefix_length, 6);
1102
1103         SELECT value::INT INTO maxED FROM config.internal_flag WHERE name = 'symspell.max_edit_distance' AND enabled;
1104         maxED := COALESCE(maxED, 3);
1105
1106         input := evergreen.lowercase(full_input);
1107         word_list := ARRAY_AGG(x) FROM search.symspell_parse_words_distinct(input) x;
1108     
1109         IF CARDINALITY(word_list) > 1 AND include_phrases THEN
1110             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(input, source_class, TRUE, prefix_length, maxED);
1111         END IF;
1112
1113         FOREACH word IN ARRAY word_list LOOP
1114             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(word, source_class, FALSE, prefix_length, maxED);
1115         END LOOP;
1116     END IF;
1117
1118     IF old_input IS NOT NULL THEN
1119         input := evergreen.lowercase(old_input);
1120
1121         FOR word IN SELECT x FROM search.symspell_parse_words_distinct(input) x LOOP
1122             entry.prefix_key := word;
1123
1124             entry.keyword_count := 0;
1125             entry.title_count := 0;
1126             entry.author_count := 0;
1127             entry.subject_count := 0;
1128             entry.series_count := 0;
1129             entry.identifier_count := 0;
1130
1131             entry.keyword_suggestions := '{}';
1132             entry.title_suggestions := '{}';
1133             entry.author_suggestions := '{}';
1134             entry.subject_suggestions := '{}';
1135             entry.series_suggestions := '{}';
1136             entry.identifier_suggestions := '{}';
1137
1138             IF source_class = 'keyword' THEN entry.keyword_count := -1; END IF;
1139             IF source_class = 'title' THEN entry.title_count := -1; END IF;
1140             IF source_class = 'author' THEN entry.author_count := -1; END IF;
1141             IF source_class = 'subject' THEN entry.subject_count := -1; END IF;
1142             IF source_class = 'series' THEN entry.series_count := -1; END IF;
1143             IF source_class = 'identifier' THEN entry.identifier_count := -1; END IF;
1144
1145             RETURN NEXT entry;
1146         END LOOP;
1147     END IF;
1148 END;
1149 $F$ LANGUAGE PLPGSQL;
1150
1151 CREATE OR REPLACE FUNCTION search.symspell_build_and_merge_entries (
1152     full_input      TEXT,
1153     source_class    TEXT,
1154     old_input       TEXT DEFAULT NULL,
1155     include_phrases BOOL DEFAULT FALSE
1156 ) RETURNS SETOF search.symspell_dictionary AS $F$
1157 DECLARE
1158     new_entry       RECORD;
1159     conflict_entry  RECORD;
1160 BEGIN
1161
1162     IF full_input = old_input THEN -- neither NULL, and are the same
1163         RETURN;
1164     END IF;
1165
1166     FOR new_entry IN EXECUTE $q$
1167         SELECT  count,
1168                 prefix_key,
1169                 evergreen.text_array_merge_unique(s,'{}') suggestions
1170           FROM  (SELECT prefix_key,
1171                         ARRAY_AGG($q$ || source_class || $q$_suggestions[1]) s,
1172                         SUM($q$ || source_class || $q$_count) count
1173                   FROM  search.symspell_build_entries($1, $2, $3, $4)
1174                   GROUP BY 1) x
1175         $q$ USING full_input, source_class, old_input, include_phrases
1176     LOOP
1177         EXECUTE $q$
1178             SELECT  prefix_key,
1179                     $q$ || source_class || $q$_suggestions suggestions,
1180                     $q$ || source_class || $q$_count count
1181               FROM  search.symspell_dictionary
1182               WHERE prefix_key = $1 $q$
1183             INTO conflict_entry
1184             USING new_entry.prefix_key;
1185
1186         IF new_entry.count <> 0 THEN -- Real word, and count changed
1187             IF conflict_entry.prefix_key IS NOT NULL THEN -- we'll be updating
1188                 IF conflict_entry.count > 0 THEN -- it's a real word
1189                     RETURN QUERY EXECUTE $q$
1190                         UPDATE  search.symspell_dictionary
1191                            SET  $q$ || source_class || $q$_count = $2
1192                           WHERE prefix_key = $1
1193                           RETURNING * $q$
1194                         USING new_entry.prefix_key, GREATEST(0, new_entry.count + conflict_entry.count);
1195                 ELSE -- it was a prefix key or delete-emptied word before
1196                     IF conflict_entry.suggestions @> new_entry.suggestions THEN -- already have all suggestions here...
1197                         RETURN QUERY EXECUTE $q$
1198                             UPDATE  search.symspell_dictionary
1199                                SET  $q$ || source_class || $q$_count = $2
1200                               WHERE prefix_key = $1
1201                               RETURNING * $q$
1202                             USING new_entry.prefix_key, GREATEST(0, new_entry.count);
1203                     ELSE -- new suggestion!
1204                         RETURN QUERY EXECUTE $q$
1205                             UPDATE  search.symspell_dictionary
1206                                SET  $q$ || source_class || $q$_count = $2,
1207                                     $q$ || source_class || $q$_suggestions = $3
1208                               WHERE prefix_key = $1
1209                               RETURNING * $q$
1210                             USING new_entry.prefix_key, GREATEST(0, new_entry.count), evergreen.text_array_merge_unique(conflict_entry.suggestions,new_entry.suggestions);
1211                     END IF;
1212                 END IF;
1213             ELSE
1214                 -- We keep the on-conflict clause just in case...
1215                 RETURN QUERY EXECUTE $q$
1216                     INSERT INTO search.symspell_dictionary AS d (
1217                         $q$ || source_class || $q$_count,
1218                         prefix_key,
1219                         $q$ || source_class || $q$_suggestions
1220                     ) VALUES ( $1, $2, $3 ) ON CONFLICT (prefix_key) DO
1221                         UPDATE SET  $q$ || source_class || $q$_count = d.$q$ || source_class || $q$_count + EXCLUDED.$q$ || source_class || $q$_count,
1222                                     $q$ || source_class || $q$_suggestions = evergreen.text_array_merge_unique(d.$q$ || source_class || $q$_suggestions, EXCLUDED.$q$ || source_class || $q$_suggestions)
1223                         RETURNING * $q$
1224                     USING new_entry.count, new_entry.prefix_key, new_entry.suggestions;
1225             END IF;
1226         ELSE -- key only, or no change
1227             IF conflict_entry.prefix_key IS NOT NULL THEN -- we'll be updating
1228                 IF NOT conflict_entry.suggestions @> new_entry.suggestions THEN -- There are new suggestions
1229                     RETURN QUERY EXECUTE $q$
1230                         UPDATE  search.symspell_dictionary
1231                            SET  $q$ || source_class || $q$_suggestions = $2
1232                           WHERE prefix_key = $1
1233                           RETURNING * $q$
1234                         USING new_entry.prefix_key, evergreen.text_array_merge_unique(conflict_entry.suggestions,new_entry.suggestions);
1235                 END IF;
1236             ELSE
1237                 RETURN QUERY EXECUTE $q$
1238                     INSERT INTO search.symspell_dictionary AS d (
1239                         $q$ || source_class || $q$_count,
1240                         prefix_key,
1241                         $q$ || source_class || $q$_suggestions
1242                     ) VALUES ( $1, $2, $3 ) ON CONFLICT (prefix_key) DO -- key exists, suggestions may be added due to this entry
1243                         UPDATE SET  $q$ || source_class || $q$_suggestions = evergreen.text_array_merge_unique(d.$q$ || source_class || $q$_suggestions, EXCLUDED.$q$ || source_class || $q$_suggestions)
1244                     RETURNING * $q$
1245                     USING new_entry.count, new_entry.prefix_key, new_entry.suggestions;
1246             END IF;
1247         END IF;
1248     END LOOP;
1249 END;
1250 $F$ LANGUAGE PLPGSQL;
1251
1252 CREATE OR REPLACE FUNCTION search.symspell_maintain_entries () RETURNS TRIGGER AS $f$
1253 DECLARE
1254     search_class    TEXT;
1255     new_value       TEXT := NULL;
1256     old_value       TEXT := NULL;
1257 BEGIN
1258     search_class := COALESCE(TG_ARGV[0], SPLIT_PART(TG_TABLE_NAME,'_',1));
1259
1260     IF TG_OP IN ('INSERT', 'UPDATE') THEN
1261         new_value := NEW.value;
1262     END IF;
1263
1264     IF TG_OP IN ('DELETE', 'UPDATE') THEN
1265         old_value := OLD.value;
1266     END IF;
1267
1268     PERFORM * FROM search.symspell_build_and_merge_entries(new_value, search_class, old_value);
1269
1270     RETURN NULL; -- always fired AFTER
1271 END;
1272 $f$ LANGUAGE PLPGSQL;
1273
1274 CREATE TRIGGER maintain_symspell_entries_tgr
1275     AFTER INSERT OR UPDATE OR DELETE ON metabib.title_field_entry
1276     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1277
1278 CREATE TRIGGER maintain_symspell_entries_tgr
1279     AFTER INSERT OR UPDATE OR DELETE ON metabib.author_field_entry
1280     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1281
1282 CREATE TRIGGER maintain_symspell_entries_tgr
1283     AFTER INSERT OR UPDATE OR DELETE ON metabib.subject_field_entry
1284     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1285
1286 CREATE TRIGGER maintain_symspell_entries_tgr
1287     AFTER INSERT OR UPDATE OR DELETE ON metabib.series_field_entry
1288     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1289
1290 CREATE TRIGGER maintain_symspell_entries_tgr
1291     AFTER INSERT OR UPDATE OR DELETE ON metabib.keyword_field_entry
1292     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1293
1294 CREATE TRIGGER maintain_symspell_entries_tgr
1295     AFTER INSERT OR UPDATE OR DELETE ON metabib.identifier_field_entry
1296     FOR EACH ROW EXECUTE PROCEDURE search.symspell_maintain_entries();
1297
1298
1299 /* This will generate the queries needed to generate the /file/ that can
1300  * be used to populate the dictionary table.
1301
1302 select $z$select $y$select $y$||x.id||$y$, '$z$||x.x||$z$', count(*) from search.symspell_build_and_merge_entries($x$$y$ || x.value||$y$$x$, '$z$||x||$z$');$y$ from metabib.$z$||x||$z$_field_entry x;$z$ from (select 'keyword'::text x union select 'title' union select 'author' union select 'subject' union select 'series' union select 'identifier') x;
1303
1304 */
1305
1306 \qecho ''
1307 \qecho 'The following should be run at the end of the upgrade before any'
1308 \qecho 'reingest occurs.  Because new triggers are installed already,'
1309 \qecho 'updates to indexed strings will cause zero-count dictionary entries'
1310 \qecho 'to be recorded which will require updating every row again (or'
1311 \qecho 'starting from scratch) so best to do this before other batch'
1312 \qecho 'changes.  A later reingest that does not significantly change'
1313 \qecho 'indexed strings will /not/ cause table bloat here, and will be'
1314 \qecho 'as fast as normal.  A copy of the SQL in a ready-to-use, non-escaped'
1315 \qecho 'form is available inside a comment at the end of this upgrade sub-'
1316 \qecho 'script so you do not need to copy this comment from the psql ouptut.'
1317 \qecho ''
1318 \qecho '\\a'
1319 \qecho '\\t'
1320 \qecho ''
1321 \qecho '\\o title'
1322 \qecho 'select value from metabib.title_field_entry;'
1323 \qecho '\\o author'
1324 \qecho 'select value from metabib.author_field_entry;'
1325 \qecho '\\o subject'
1326 \qecho 'select value from metabib.subject_field_entry;'
1327 \qecho '\\o series'
1328 \qecho 'select value from metabib.series_field_entry;'
1329 \qecho '\\o identifier'
1330 \qecho 'select value from metabib.identifier_field_entry;'
1331 \qecho '\\o keyword'
1332 \qecho 'select value from metabib.keyword_field_entry;'
1333 \qecho ''
1334 \qecho '\\o'
1335 \qecho '\\a'
1336 \qecho '\\t'
1337 \qecho ''
1338 \qecho '// Then, at the command line:'
1339 \qecho ''
1340 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl title > title.sql'
1341 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl author > author.sql'
1342 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl subject > subject.sql'
1343 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl series > series.sql'
1344 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl identifier > identifier.sql'
1345 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl keyword > keyword.sql'
1346 \qecho ''
1347 \qecho '// And, back in psql'
1348 \qecho ''
1349 \qecho 'ALTER TABLE search.symspell_dictionary SET UNLOGGED;'
1350 \qecho 'TRUNCATE search.symspell_dictionary;'
1351 \qecho ''
1352 \qecho '\\i identifier.sql'
1353 \qecho '\\i author.sql'
1354 \qecho '\\i title.sql'
1355 \qecho '\\i subject.sql'
1356 \qecho '\\i series.sql'
1357 \qecho '\\i keyword.sql'
1358 \qecho ''
1359 \qecho 'CLUSTER search.symspell_dictionary USING symspell_dictionary_pkey;'
1360 \qecho 'REINDEX TABLE search.symspell_dictionary;'
1361 \qecho 'ALTER TABLE search.symspell_dictionary SET LOGGED;'
1362 \qecho 'VACUUM ANALYZE search.symspell_dictionary;'
1363 \qecho ''
1364 \qecho 'DROP TABLE search.symspell_dictionary_partial_title;'
1365 \qecho 'DROP TABLE search.symspell_dictionary_partial_author;'
1366 \qecho 'DROP TABLE search.symspell_dictionary_partial_subject;'
1367 \qecho 'DROP TABLE search.symspell_dictionary_partial_series;'
1368 \qecho 'DROP TABLE search.symspell_dictionary_partial_identifier;'
1369 \qecho 'DROP TABLE search.symspell_dictionary_partial_keyword;'
1370
1371 SELECT evergreen.upgrade_deps_block_check('1258', :eg_version);
1372
1373 UPDATE config.metabib_field 
1374 SET xpath =  '//*[@tag=''260'' or @tag=''264''][1]'
1375 WHERE id = 52 AND xpath = '//*[@tag=''260'']';
1376
1377 COMMIT;
1378
1379 -- Update auditor tables to catch changes to source tables.
1380 --   Can be removed/skipped if there were no schema changes.
1381 SELECT auditor.update_auditors();
1382
1383 \qecho
1384 \qecho This is a partial record attribute reingest of your bib records.
1385 \qecho It may take a while.
1386 \qecho You may cancel now without losing the effect of the rest of the
1387 \qecho upgrade script, and arrange the reingest later.
1388 SELECT COUNT(metabib.reingest_record_attributes(bre.id))
1389     FROM biblio.record_entry bre
1390     JOIN metabib.record_attr_flat mraf ON (bre.id = mraf.id)
1391     WHERE deleted IS FALSE
1392     AND attr = 'item_type'
1393     AND value = 'g';
1394
1395