1 --Upgrade Script for 3.3.4 to 3.3.5
2 \set eg_version '''3.3.5'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.3.5', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1185', :eg_version); -- csharp / gmcharlt / jboyer
8 ALTER FUNCTION permission.grp_descendants( INT ) STABLE;
11 SELECT evergreen.upgrade_deps_block_check('1187', :eg_version);
13 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
18 -- If there are any renewals for this circulation, don't archive or delete
19 -- it yet. We'll do so later, when we archive and delete the renewals.
22 FROM action.circulation
23 WHERE parent_circ = OLD.id
27 RETURN NULL; -- don't delete
30 -- Archive a copy of the old row to action.aged_circulation
32 INSERT INTO action.aged_circulation
33 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
34 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
35 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
36 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
37 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
38 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
39 auto_renewal, auto_renewal_remaining)
41 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
42 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
43 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
44 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
45 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
46 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
47 auto_renewal, auto_renewal_remaining
48 FROM action.all_circulation WHERE id = OLD.id;
52 $$ LANGUAGE 'plpgsql';
56 SELECT evergreen.upgrade_deps_block_check('1188', :eg_version);
58 UPDATE action.circulation SET auto_renewal = FALSE WHERE auto_renewal IS NULL;
60 UPDATE action.aged_circulation SET auto_renewal = FALSE WHERE auto_renewal IS NULL;
63 -- The following two changes cannot occur in a transaction with the
64 -- above updates because we will get an error about not being able to
65 -- alter a table with pending transactions. They also need to occur
66 -- after the above updates or the SET NOT NULL change will fail.
68 ALTER TABLE action.circulation ALTER COLUMN auto_renewal SET DEFAULT FALSE;
69 ALTER TABLE action.circulation ALTER COLUMN auto_renewal SET NOT NULL;
71 ALTER TABLE action.aged_circulation ALTER COLUMN auto_renewal SET DEFAULT FALSE;
72 ALTER TABLE action.aged_circulation ALTER COLUMN auto_renewal SET NOT NULL;
74 SELECT evergreen.upgrade_deps_block_check('1189', :eg_version);
76 CREATE OR REPLACE VIEW action.open_circulation AS
78 FROM action.circulation
79 WHERE checkin_time IS NULL
82 CREATE OR REPLACE VIEW action.billable_circulations AS
84 FROM action.circulation
85 WHERE xact_finish IS NULL;
87 CREATE OR REPLACE VIEW reporter.overdue_circs AS
89 FROM "action".circulation
90 WHERE checkin_time is null
91 AND (stop_fines NOT IN ('LOST','CLAIMSRETURNED') OR stop_fines IS NULL)
94 CREATE OR REPLACE VIEW reporter.circ_type AS
96 CASE WHEN opac_renewal OR phone_renewal OR desk_renewal OR auto_renewal
100 FROM action.circulation;
103 SELECT evergreen.upgrade_deps_block_check('1190', :eg_version);
105 UPDATE action.circulation SET desk_renewal = FALSE WHERE auto_renewal IS TRUE;
107 UPDATE action.aged_circulation SET desk_renewal = FALSE WHERE auto_renewal IS TRUE;
110 SELECT evergreen.upgrade_deps_block_check('1191', :eg_version);
112 INSERT INTO permission.perm_list ( id, code, description ) SELECT DISTINCT
114 'EDIT_SELF_IN_CLIENT',
115 oils_i18n_gettext(619,
116 'Allow a user to edit their own account in the staff client', 'ppl', 'description'
118 FROM permission.perm_list
119 WHERE NOT EXISTS (SELECT 1 FROM permission.perm_list WHERE code = 'EDIT_SELF_IN_CLIENT');
122 SELECT evergreen.upgrade_deps_block_check('1193', :eg_version);
124 INSERT INTO config.workstation_setting_type
125 (name, grp, datatype, label)
127 'eg.grid.circ.patron.xact_details_details_bills', 'gui', 'object',
129 'eg.grid.circ.patron.xact_details_details_bills',
130 'Grid Config: circ.patron.xact_details_details_bills',
133 'eg.grid.circ.patron.xact_details_details_payments', 'gui', 'object',
135 'eg.grid.circ.patron.xact_details_details_payments',
136 'Grid Config: circ.patron.xact_details_details_payments',
141 SELECT evergreen.upgrade_deps_block_check('1195', :eg_version);
143 CREATE OR REPLACE FUNCTION metabib.suggest_browse_entries(raw_query_text text, search_class text, headline_opts text, visibility_org integer, query_limit integer, normalization integer)
144 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
147 prepared_query_texts TEXT[];
150 opac_visibility_join TEXT;
151 search_class_join TEXT;
155 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
157 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
158 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
160 visibility_org := NULLIF(visibility_org,-1);
161 IF visibility_org IS NOT NULL THEN
162 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
164 opac_visibility_join := '';
166 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
168 b_tests := search.calculate_visibility_attribute_test(
170 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(visibility_org))
173 b_tests := search.calculate_visibility_attribute_test(
175 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(visibility_org))
178 opac_visibility_join := '
179 LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
180 LEFT JOIN biblio.record_entry b ON (b.id = x.source)
181 JOIN vm ON (acvac.vis_attr_vector @@
182 (vm.c_attrs || $$&$$ ||
183 search.calculate_visibility_attribute_test(
185 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
188 ) OR (b.vis_attr_vector @@ $$' || b_tests || '$$::query_int)
192 opac_visibility_join := '';
195 -- The following determines whether we only provide suggestsons matching
196 -- the user's selected search_class, or whether we show other suggestions
197 -- too. The reason for MIN() is that for search_classes like
198 -- 'title|proper|uniform' you would otherwise get multiple rows. The
199 -- implication is that if title as a class doesn't have restrict,
200 -- nor does the proper field, but the uniform field does, you're going
201 -- to get 'false' for your overall evaluation of 'should we restrict?'
202 -- To invert that, change from MIN() to MAX().
206 MIN(cmc.restrict::INT) AS restrict_class,
207 MIN(cmf.restrict::INT) AS restrict_field
208 FROM metabib.search_class_to_registered_components(search_class)
209 AS _registered (field_class TEXT, field INT)
211 config.metabib_class cmc ON (cmc.name = _registered.field_class)
213 config.metabib_field cmf ON (cmf.id = _registered.field);
215 -- evaluate 'should we restrict?'
216 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
217 search_class_join := '
219 metabib.search_class_to_registered_components($2)
220 AS _registered (field_class TEXT, field INT) ON (
221 (_registered.field IS NULL AND
222 _registered.field_class = cmf.field_class) OR
223 (_registered.field = cmf.id)
227 search_class_join := '
229 metabib.search_class_to_registered_components($2)
230 AS _registered (field_class TEXT, field INT) ON (
231 _registered.field_class = cmc.name
236 RETURN QUERY EXECUTE '
237 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
238 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
247 TS_HEADLINE(value, $7, $3)
248 FROM (SELECT DISTINCT
251 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
252 _registered.field = cmf.id AS restrict,
254 TS_RANK_CD(mbe.index_vector, $1, $6),
257 FROM metabib.browse_entry_def_map mbedm
258 JOIN mbe ON (mbe.id = mbedm.entry)
259 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
260 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
261 ' || search_class_join || '
262 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
264 ' || opac_visibility_join || '
265 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
267 ' -- sic, repeat the order by clause in the outer select too
269 query, search_class, headline_opts,
270 visibility_org, query_limit, normalization, plain_query
274 -- buoyant AND chosen class = match class
275 -- chosen field = match field
282 $f$ LANGUAGE plpgsql ROWS 10;