2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 CREATE OR REPLACE FUNCTION public.non_filing_normalize ( TEXT, "char" ) RETURNS TEXT AS $$
30 WHEN $2::INT NOT BETWEEN 48 AND 57 THEN 1
31 ELSE $2::TEXT::INT + 1
34 $$ LANGUAGE SQL STRICT IMMUTABLE;
36 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
39 use Unicode::Normalize;
42 my $str = decode_utf8(shift);
45 # Apply NACO normalization to input string; based on
46 # http://www.loc.gov/catdir/pcc/naco/SCA_PccNormalization_Final_revised.pdf
48 # Note that unlike a strict reading of the NACO normalization rules,
49 # output is returned as lowercase instead of uppercase for compatibility
50 # with previous versions of the Evergreen naco_normalize routine.
52 # Convert to upper-case first; even though final output will be lowercase, doing this will
53 # ensure that the German eszett (ß) and certain ligatures (ff, fi, ffl, etc.) will be handled correctly.
54 # If there are any bugs in Perl's implementation of upcasing, they will be passed through here.
57 # remove non-filing strings
58 $str =~ s/\x{0098}.*?\x{009C}//g;
62 # additional substitutions - 3.6.
63 $str =~ s/\x{00C6}/AE/g;
64 $str =~ s/\x{00DE}/TH/g;
65 $str =~ s/\x{0152}/OE/g;
66 $str =~ tr/\x{0110}\x{00D0}\x{00D8}\x{0141}\x{2113}\x{02BB}\x{02BC}]['/DDOLl/d;
68 # transformations based on Unicode category codes
69 $str =~ s/[\p{Cc}\p{Cf}\p{Co}\p{Cs}\p{Lm}\p{Mc}\p{Me}\p{Mn}]//g;
71 if ($sf && $sf =~ /^a/o) {
72 my $commapos = index($str, ',');
74 if ($commapos != length($str) - 1) {
75 $str =~ s/,/\x07/; # preserve first comma
80 # since we've stripped out the control characters, we can now
81 # use a few as placeholders temporarily
82 $str =~ tr/+&@\x{266D}\x{266F}#/\x01\x02\x03\x04\x05\x06/;
83 $str =~ s/[\p{Pc}\p{Pd}\p{Pe}\p{Pf}\p{Pi}\p{Po}\p{Ps}\p{Sk}\p{Sm}\p{So}\p{Zl}\p{Zp}\p{Zs}]/ /g;
84 $str =~ tr/\x01\x02\x03\x04\x05\x06\x07/+&@\x{266D}\x{266F}#,/;
87 $str =~ tr/\x{0660}-\x{0669}\x{06F0}-\x{06F9}\x{07C0}-\x{07C9}\x{0966}-\x{096F}\x{09E6}-\x{09EF}\x{0A66}-\x{0A6F}\x{0AE6}-\x{0AEF}\x{0B66}-\x{0B6F}\x{0BE6}-\x{0BEF}\x{0C66}-\x{0C6F}\x{0CE6}-\x{0CEF}\x{0D66}-\x{0D6F}\x{0E50}-\x{0E59}\x{0ED0}-\x{0ED9}\x{0F20}-\x{0F29}\x{1040}-\x{1049}\x{1090}-\x{1099}\x{17E0}-\x{17E9}\x{1810}-\x{1819}\x{1946}-\x{194F}\x{19D0}-\x{19D9}\x{1A80}-\x{1A89}\x{1A90}-\x{1A99}\x{1B50}-\x{1B59}\x{1BB0}-\x{1BB9}\x{1C40}-\x{1C49}\x{1C50}-\x{1C59}\x{A620}-\x{A629}\x{A8D0}-\x{A8D9}\x{A900}-\x{A909}\x{A9D0}-\x{A9D9}\x{AA50}-\x{AA59}\x{ABF0}-\x{ABF9}\x{FF10}-\x{FF19}/0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9/;
89 # intentionally skipping step 8 of the NACO algorithm; if the string
90 # gets normalized away, that's fine.
92 # leading and trailing spaces
98 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
100 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT ) RETURNS TEXT AS $func$
101 SELECT public.naco_normalize($1,'');
102 $func$ LANGUAGE 'sql' STRICT IMMUTABLE;
104 CREATE OR REPLACE FUNCTION public.first_word ( TEXT ) RETURNS TEXT AS $$
105 SELECT COALESCE(SUBSTRING( $1 FROM $_$^\S+$_$), '');
106 $$ LANGUAGE SQL STRICT IMMUTABLE;
108 CREATE OR REPLACE FUNCTION public.naco_normalize_keep_comma( TEXT ) RETURNS TEXT AS $func$
109 SELECT public.naco_normalize($1,'a');
110 $func$ LANGUAGE SQL STRICT IMMUTABLE;
112 CREATE OR REPLACE FUNCTION public.normalize_space( TEXT ) RETURNS TEXT AS $$
113 SELECT regexp_replace(regexp_replace(regexp_replace($1, E'\\n', ' ', 'g'), E'(?:^\\s+)|(\\s+$)', '', 'g'), E'\\s+', ' ', 'g');
114 $$ LANGUAGE SQL STRICT IMMUTABLE;
116 CREATE OR REPLACE FUNCTION public.remove_commas( TEXT ) RETURNS TEXT AS $$
117 SELECT regexp_replace($1, ',', '', 'g');
118 $$ LANGUAGE SQL STRICT IMMUTABLE;
120 CREATE OR REPLACE FUNCTION public.remove_paren_substring( TEXT ) RETURNS TEXT AS $func$
121 SELECT regexp_replace($1, $$\([^)]+\)$$, '', 'g');
122 $func$ LANGUAGE SQL STRICT IMMUTABLE;
124 CREATE OR REPLACE FUNCTION public.remove_whitespace( TEXT ) RETURNS TEXT AS $$
125 SELECT regexp_replace(normalize_space($1), E'\\s+', '', 'g');
126 $$ LANGUAGE SQL STRICT IMMUTABLE;
128 CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
130 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
132 CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
134 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
136 CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
137 use Unicode::Normalize;
143 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
145 CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
146 use Unicode::Normalize;
149 $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
152 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
154 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
157 $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//og;
159 if ($txt =~ /(\d{3}(?:\.\d+)?)/o) {
162 return (split /\s+/, $txt)[0];
164 $$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
166 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT, INT ) RETURNS TEXT AS $$
167 SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2);
168 $$ LANGUAGE SQL STRICT IMMUTABLE;
170 CREATE OR REPLACE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
174 $$ language 'plpgsql';
176 CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT, INT ) RETURNS SETOF actor.org_unit AS $$
177 WITH RECURSIVE descendant_depth AS (
181 FROM actor.org_unit ou
182 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
183 JOIN anscestor_depth ad ON (ad.id = ou.id)
189 FROM actor.org_unit ou
190 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
191 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
192 ), anscestor_depth AS (
196 FROM actor.org_unit ou
197 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
203 FROM actor.org_unit ou
204 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
205 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
206 ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id);
207 $$ LANGUAGE SQL ROWS 1;
209 CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT ) RETURNS SETOF actor.org_unit AS $$
210 WITH RECURSIVE descendant_depth AS (
214 FROM actor.org_unit ou
215 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
221 FROM actor.org_unit ou
222 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
223 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
224 ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id);
225 $$ LANGUAGE SQL ROWS 1;
227 CREATE OR REPLACE FUNCTION actor.org_unit_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
228 WITH RECURSIVE org_unit_descendants_distance(id, distance) AS (
231 SELECT ou.id, oudd.distance+1
232 FROM actor.org_unit ou JOIN org_unit_descendants_distance oudd ON (ou.parent_ou = oudd.id)
234 SELECT * FROM org_unit_descendants_distance;
235 $$ LANGUAGE SQL STABLE ROWS 1;
237 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors( INT ) RETURNS SETOF actor.org_unit AS $$
238 WITH RECURSIVE anscestor_depth AS (
241 FROM actor.org_unit ou
246 FROM actor.org_unit ou
247 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
248 ) SELECT ou.* FROM actor.org_unit ou JOIN anscestor_depth USING (id);
249 $$ LANGUAGE SQL ROWS 1;
251 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
253 FROM actor.org_unit a
254 WHERE id = ( SELECT FIRST(x.id)
255 FROM actor.org_unit_ancestors($1) x
256 JOIN actor.org_unit_type y
257 ON x.ou_type = y.id AND y.depth = $2);
258 $$ LANGUAGE SQL STABLE;
260 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
261 WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
264 SELECT ou.parent_ou, ouad.distance+1
265 FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
266 WHERE ou.parent_ou IS NOT NULL
268 SELECT * FROM org_unit_ancestors_distance;
269 $$ LANGUAGE SQL STABLE ROWS 1;
271 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
273 FROM actor.org_unit_ancestors($1)
276 FROM actor.org_unit_descendants($1);
277 $$ LANGUAGE SQL STABLE ROWS 1;
279 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
280 SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
281 $$ LANGUAGE SQL STABLE ROWS 1;
283 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
285 FROM actor.org_unit_ancestors($1)
288 FROM actor.org_unit_ancestors($2);
289 $$ LANGUAGE SQL STABLE ROWS 1;
291 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
293 FROM actor.org_unit_ancestors($1)
296 FROM actor.org_unit_ancestors($2);
297 $$ LANGUAGE SQL STABLE ROWS 1;
299 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
300 SELECT COUNT(id)::INT FROM (
301 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
303 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
305 $$ LANGUAGE SQL STABLE;
307 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
314 SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
319 SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
320 EXIT WHEN cur_org IS NULL;
324 $$ LANGUAGE plpgsql STABLE ROWS 1;
326 COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$
327 Search "up" the org_unit tree until we find the first occurrence of an
328 org_unit_setting with the given name.
331 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
336 asset_barcodes TEXT[];
337 actor_barcodes TEXT[];
338 do_asset BOOL = false;
339 do_serial BOOL = false;
340 do_booking BOOL = false;
341 do_actor BOOL = false;
342 completion_set config.barcode_completion%ROWTYPE;
345 IF position('asset' in type) > 0 THEN
348 IF position('serial' in type) > 0 THEN
351 IF position('booking' in type) > 0 THEN
354 IF do_asset OR do_serial OR do_booking THEN
355 asset_barcodes = asset_barcodes || in_barcode;
357 IF position('actor' in type) > 0 THEN
359 actor_barcodes = actor_barcodes || in_barcode;
362 barcode_len := length(in_barcode);
364 FOR completion_set IN
365 SELECT * FROM config.barcode_completion
367 AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
369 IF completion_set.prefix IS NULL THEN
370 completion_set.prefix := '';
372 IF completion_set.suffix IS NULL THEN
373 completion_set.suffix := '';
375 IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
376 cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
378 completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
379 IF completion_len >= barcode_len THEN
380 IF completion_set.padding_end THEN
381 cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
383 cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
385 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
388 IF completion_set.actor THEN
389 actor_barcodes = actor_barcodes || cur_barcode;
391 IF completion_set.asset THEN
392 asset_barcodes = asset_barcodes || cur_barcode;
396 IF do_asset AND do_serial THEN
397 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
398 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
400 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
402 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
405 RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
408 RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE c.barcode = ANY(actor_barcodes) AND c.active AND NOT u.deleted ORDER BY usr;
414 COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$
415 Given user input, find an appropriate barcode in the proper class.
417 Will add prefix/suffix information to do so, and return all results.