1 -- Copyright 2009-2012, Equinox Software, Inc.
3 -- This program is free software; you can redistribute it and/or
4 -- modify it under the terms of the GNU General Public License
5 -- as published by the Free Software Foundation; either version 2
6 -- of the License, or (at your option) any later version.
8 -- This program is distributed in the hope that it will be useful,
9 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 -- GNU General Public License for more details.
13 -- You should have received a copy of the GNU General Public License
14 -- along with this program; if not, write to the Free Software
15 -- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 --------------------------------------------------------------------------
18 -- An example of how to use:
20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
22 -- SELECT migration_tools.init('foo');
23 -- SELECT migration_tools.build('foo');
24 -- SELECT * FROM foo.fields_requiring_mapping;
26 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS (foo.asset_copy);
27 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
28 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
30 CREATE SCHEMA migration_tools;
32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
34 migration_schema ALIAS FOR $1;
38 EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
47 migration_schema ALIAS FOR $1;
51 EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
61 migration_schema ALIAS FOR $1;
65 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
71 migration_schema ALIAS FOR $1;
75 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
76 --RAISE INFO '%', sql;
78 GET DIAGNOSTICS nrows = ROW_COUNT;
79 PERFORM migration_tools.log(migration_schema,sql,nrows);
82 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
88 migration_schema ALIAS FOR $1;
92 EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
93 RAISE INFO 'debug_exec sql = %', sql;
95 GET DIAGNOSTICS nrows = ROW_COUNT;
96 PERFORM migration_tools.log(migration_schema,sql,nrows);
99 RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
105 migration_schema ALIAS FOR $1;
108 EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
109 EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
110 EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
112 SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
116 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
118 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
119 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
120 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint'' );' );
121 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
122 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
123 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
124 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
125 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
128 transcribed_perm_group TEXT,
136 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_profile_map'', ''base_profile_map'' );' );
137 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_item_dynamic_field_map;' );
138 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_item_dynamic_field_map (
140 evergreen_field TEXT,
141 evergreen_value TEXT,
142 evergreen_datatype TEXT,
150 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf1_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field1,legacy_value1);' );
151 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf2_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field2,legacy_value2);' );
152 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf3_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field3,legacy_value3);' );
153 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_item_dynamic_field_map'', ''base_item_dynamic_field_map'' );' );
154 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_copy_location_map;' );
155 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_copy_location_map (
158 holdable BOOLEAN NOT NULL DEFAULT TRUE,
159 hold_verify BOOLEAN NOT NULL DEFAULT FALSE,
160 opac_visible BOOLEAN NOT NULL DEFAULT TRUE,
161 circulate BOOLEAN NOT NULL DEFAULT TRUE,
162 transcribed_location TEXT,
170 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf1_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field1,legacy_value1);' );
171 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf2_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field2,legacy_value2);' );
172 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf3_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field3,legacy_value3);' );
173 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_loc_idx ON ' || migration_schema || '.base_copy_location_map (transcribed_location);' );
174 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_copy_location_map'', ''base_copy_location_map'' );' );
175 PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_circ_field_map;' );
176 PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_circ_field_map (
194 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf1_idx ON ' || migration_schema || '.base_circ_field_map (item_field1,item_value1);' );
195 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf2_idx ON ' || migration_schema || '.base_circ_field_map (item_field2,item_value2);' );
196 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf3_idx ON ' || migration_schema || '.base_circ_field_map (patron_field1,patron_value1);' );
197 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf4_idx ON ' || migration_schema || '.base_circ_field_map (patron_field2,patron_value2);' );
198 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_circ_field_map'', ''base_circ_field_map'' );' );
201 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
203 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
210 migration_schema ALIAS FOR $1;
211 production_tables TEXT[];
213 --RAISE INFO 'In migration_tools.build(%)', migration_schema;
214 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
215 PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables);
216 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
217 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
218 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
219 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
220 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
221 PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
222 PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label,prefix,suffix );' );
224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
228 migration_schema ALIAS FOR $1;
229 production_tables ALIAS FOR $2;
231 --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
232 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
233 PERFORM migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
240 migration_schema ALIAS FOR $1;
241 production_table ALIAS FOR $2;
242 base_staging_table TEXT;
245 base_staging_table = REPLACE( production_table, '.', '_' );
246 --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
247 PERFORM migration_tools.exec( $1, 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
248 PERFORM migration_tools.exec( $1, '
249 INSERT INTO ' || migration_schema || '.fields_requiring_mapping
250 SELECT table_schema, table_name, column_name, data_type
251 FROM information_schema.columns
252 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
255 SELECT table_schema, table_name, column_name, data_type
256 FROM information_schema.columns
257 WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
259 PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
264 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
266 migration_schema ALIAS FOR $1;
267 production_tables TEXT[];
269 --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
270 SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
271 FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
272 PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
275 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
277 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
279 migration_schema ALIAS FOR $1;
280 production_table ALIAS FOR $2;
281 base_staging_table TEXT;
284 base_staging_table = REPLACE( production_table, '.', '_' );
285 --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
286 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
288 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
290 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
292 full_name TEXT := $1;
294 family_name TEXT := '';
295 first_given_name TEXT := '';
296 second_given_name TEXT := '';
301 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
302 IF temp ilike '%MR.%' THEN
304 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
306 IF temp ilike '%MRS.%' THEN
308 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
310 IF temp ilike '%MS.%' THEN
312 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
314 IF temp ilike '%DR.%' THEN
316 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
318 IF temp ilike '%JR%' THEN
320 temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
322 IF temp ilike '%JR,%' THEN
324 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
326 IF temp ilike '%SR%' THEN
328 temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
330 IF temp ilike '%SR,%' THEN
332 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
334 IF temp ~ E'\\sII$' THEN
336 temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
338 IF temp ~ E'\\sIII$' THEN
340 temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
342 IF temp ~ E'\\sIV$' THEN
344 temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
347 family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
348 first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
349 second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
351 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
353 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
355 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
357 full_name TEXT := $1;
359 family_name TEXT := '';
360 first_given_name TEXT := '';
361 second_given_name TEXT := '';
366 -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
367 IF temp ilike '%MR.%' THEN
369 temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
371 IF temp ilike '%MRS.%' THEN
373 temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
375 IF temp ilike '%MS.%' THEN
377 temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
379 IF temp ilike '%DR.%' THEN
381 temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
383 IF temp ilike '%JR.%' THEN
385 temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
387 IF temp ilike '%JR,%' THEN
389 temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
391 IF temp ilike '%SR.%' THEN
393 temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
395 IF temp ilike '%SR,%' THEN
397 temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
399 IF temp like '%III%' THEN
401 temp := REGEXP_REPLACE( temp, E'III', '' );
403 IF temp like '%II%' THEN
405 temp := REGEXP_REPLACE( temp, E'II', '' );
407 IF temp like '%IV%' THEN
409 temp := REGEXP_REPLACE( temp, E'IV', '' );
412 temp := REGEXP_REPLACE( temp, '\(\)', '');
413 family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
414 family_name := REGEXP_REPLACE( family_name, ',', '' );
415 first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
416 first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
417 second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
418 second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
420 RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
422 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
425 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
427 city_state_zip TEXT := $1;
432 zip := CASE WHEN city_state_zip ~ E'\\d\\d\\d\\d\\d' THEN REGEXP_REPLACE( city_state_zip, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) ELSE '' END;
433 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
434 IF city_state_zip ~ ',' THEN
435 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
436 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
438 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
439 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
440 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
442 IF city_state_zip ~ E'^\\S+$' THEN
443 city := city_state_zip;
446 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
447 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
451 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
453 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
455 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
459 IF o ~ E'^\\d+$' AND o !~ E'^0' AND length(o) < 19 THEN -- for reference, the max value for a bigint is 9223372036854775807. May also want to consider the case where folks want to add prefixes to non-numeric barcodes
460 IF o::BIGINT < t THEN
467 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
469 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
471 migration_schema ALIAS FOR $1;
475 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
480 $$ LANGUAGE PLPGSQL STRICT STABLE;
482 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
484 migration_schema ALIAS FOR $1;
488 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
493 $$ LANGUAGE PLPGSQL STRICT STABLE;
495 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
497 migration_schema ALIAS FOR $1;
501 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
506 $$ LANGUAGE PLPGSQL STRICT STABLE;
508 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
510 migration_schema ALIAS FOR $1;
514 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
519 $$ LANGUAGE PLPGSQL STRICT STABLE;
521 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
523 migration_schema ALIAS FOR $1;
525 patron_table ALIAS FOR $2;
526 default_patron_profile ALIAS FOR $3;
529 sql_where1 TEXT := '';
530 sql_where2 TEXT := '';
531 sql_where3 TEXT := '';
534 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
536 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
538 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
539 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
540 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
541 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
542 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
543 --RAISE INFO 'sql = %', sql;
544 PERFORM migration_tools.exec( $1, sql );
546 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
548 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
550 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
553 $$ LANGUAGE PLPGSQL STRICT STABLE;
555 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
557 migration_schema ALIAS FOR $1;
559 item_table ALIAS FOR $2;
562 sql_where1 TEXT := '';
563 sql_where2 TEXT := '';
564 sql_where3 TEXT := '';
567 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
569 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
571 sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE ';
572 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
573 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
574 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
575 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
576 --RAISE INFO 'sql = %', sql;
577 PERFORM migration_tools.exec( $1, sql );
580 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
582 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
585 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
587 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
589 migration_schema ALIAS FOR $1;
590 base_copy_location_map TEXT;
591 item_table ALIAS FOR $2;
594 sql_where1 TEXT := '';
595 sql_where2 TEXT := '';
596 sql_where3 TEXT := '';
599 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
601 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
603 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
604 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
605 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
606 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
607 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
608 --RAISE INFO 'sql = %', sql;
609 PERFORM migration_tools.exec( $1, sql );
612 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
614 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
617 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
619 -- circulate loan period max renewals max out fine amount fine interval max fine item field 1 item value 1 item field 2 item value 2 patron field 1 patron value 1 patron field 2 patron value 2
620 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
622 migration_schema ALIAS FOR $1;
624 circ_table ALIAS FOR $2;
625 item_table ALIAS FOR $3;
626 patron_table ALIAS FOR $4;
629 sql_where1 TEXT := '';
630 sql_where2 TEXT := '';
631 sql_where3 TEXT := '';
632 sql_where4 TEXT := '';
635 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
637 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
639 sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND ';
640 sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1);
641 sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2);
642 sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1);
643 sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2);
644 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';';
645 --RAISE INFO 'sql = %', sql;
646 PERFORM migration_tools.exec( $1, sql );
649 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
651 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
654 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
657 -- $barcode source barcode
658 -- $prefix prefix to add to barcode, NULL = add no prefix
659 -- $maxlen maximum length of barcode; default to 14 if left NULL
660 -- $pad padding string to apply to left of source barcode before adding
661 -- prefix and suffix; set to NULL or '' if no padding is desired
662 -- $suffix suffix to add to barcode, NULL = add no suffix
664 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
665 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
667 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
668 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
671 return unless defined $barcode;
673 $prefix = '' unless defined $prefix;
675 $pad = '0' unless defined $pad;
676 $suffix = '' unless defined $suffix;
678 # bail out if adding prefix and suffix would bring new barcode over max length
679 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
681 my $new_barcode = $barcode;
683 my $pad_length = $maxlen - length($prefix) - length($suffix);
684 if (length($barcode) < $pad_length) {
685 # assuming we always want padding on the left
686 # also assuming that it is possible to have the pad string be longer than 1 character
687 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
691 # bail out if adding prefix and suffix would bring new barcode over max length
692 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
694 return "$prefix$new_barcode$suffix";
695 $$ LANGUAGE PLPERLU STABLE;
697 -- remove previous version of this function
698 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
700 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
702 attempt_value ALIAS FOR $1;
703 datatype ALIAS FOR $2;
705 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
706 RETURN attempt_value;
708 WHEN OTHERS THEN RETURN NULL;
710 $$ LANGUAGE PLPGSQL STRICT STABLE;
712 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
714 attempt_value ALIAS FOR $1;
715 fail_value ALIAS FOR $2;
719 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
726 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
731 $$ LANGUAGE PLPGSQL STRICT STABLE;
733 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
735 attempt_value ALIAS FOR $1;
736 fail_value ALIAS FOR $2;
740 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
747 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
752 $$ LANGUAGE PLPGSQL STRICT STABLE;
754 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
756 attempt_value ALIAS FOR $1;
757 fail_value ALIAS FOR $2;
761 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
768 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
773 $$ LANGUAGE PLPGSQL STRICT STABLE;
775 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
777 attempt_value ALIAS FOR $1;
778 fail_value ALIAS FOR $2;
782 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
789 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
794 $$ LANGUAGE PLPGSQL STRICT STABLE;
796 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
798 attempt_value ALIAS FOR $1;
799 fail_value ALIAS FOR $2;
803 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
810 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
815 $$ LANGUAGE PLPGSQL STRICT STABLE;
817 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
819 attempt_value ALIAS FOR $1;
820 fail_value ALIAS FOR $2;
823 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
824 RAISE EXCEPTION 'too many digits';
827 EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),2))::numeric(8,2)) || '::NUMERIC(8,2) AS a;'
834 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
839 $$ LANGUAGE PLPGSQL STRICT STABLE;
841 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
843 attempt_value ALIAS FOR $1;
844 fail_value ALIAS FOR $2;
847 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
848 RAISE EXCEPTION 'too many digits';
851 EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),2))::numeric(6,2)) || '::NUMERIC(6,2) AS a;'
858 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
863 $$ LANGUAGE PLPGSQL STRICT STABLE;
865 -- add_codabar_checkdigit
866 -- $barcode source barcode
868 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
869 -- character with a checkdigit computed according to the usual algorithm for library barcodes
870 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
871 -- input string does not meet those requirements, it is returned unchanged.
873 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
876 return $barcode if $barcode !~ /^\d{13,14}$/;
877 $barcode = substr($barcode, 0, 13); # ignore 14th digit
878 my @digits = split //, $barcode;
880 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
881 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
882 my $remainder = $total % 10;
883 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
884 return $barcode . $checkdigit;
885 $$ LANGUAGE PLPERLU STRICT STABLE;
887 -- add_code39mod43_checkdigit
888 -- $barcode source barcode
890 -- If the source string is 13 or 14 characters long and contains only valid
891 -- Code 39 mod 43 characters, adds or replaces the 14th
892 -- character with a checkdigit computed according to the usual algorithm for library barcodes
893 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
894 -- input string does not meet those requirements, it is returned unchanged.
896 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
899 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
900 $barcode = substr($barcode, 0, 13); # ignore 14th character
902 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
903 my %nums = map { $valid_chars[$_] => $_ } (0..42);
906 $total += $nums{$_} foreach split(//, $barcode);
907 my $remainder = $total % 43;
908 my $checkdigit = $valid_chars[$remainder];
909 return $barcode . $checkdigit;
910 $$ LANGUAGE PLPERLU STRICT STABLE;
912 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
918 n_digits INTEGER := 0;
921 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
922 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
923 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
924 IF n_digits = 7 AND areacode <> '' THEN
925 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
926 output := (areacode || '-' || temp);
933 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
935 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
936 my ($marcxml, $pos, $value) = @_;
943 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
944 my $leader = $marc->leader();
945 substr($leader, $pos, 1) = $value;
946 $marc->leader($leader);
947 $xml = $marc->as_xml_record;
948 $xml =~ s/^<\?.+?\?>$//mo;
950 $xml =~ s/>\s+</></sgo;
953 $$ LANGUAGE PLPERLU STABLE;
955 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
956 my ($marcxml, $pos, $value) = @_;
963 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
964 my $f008 = $marc->field('008');
967 my $field = $f008->data();
968 substr($field, $pos, 1) = $value;
969 $f008->update($field);
970 $xml = $marc->as_xml_record;
971 $xml =~ s/^<\?.+?\?>$//mo;
973 $xml =~ s/>\s+</></sgo;
977 $$ LANGUAGE PLPERLU STABLE;
980 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
982 profile ALIAS FOR $1;
984 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
986 $$ LANGUAGE PLPGSQL STRICT STABLE;
989 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
991 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
993 $$ LANGUAGE PLPGSQL STRICT STABLE;
996 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
998 my ($marcxml, $tags) = @_;
1001 use MARC::File::XML;
1006 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1007 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1009 my @incumbents = ();
1011 foreach my $field ( $marc->fields() ) {
1012 push @incumbents, $field->as_formatted();
1015 foreach $field ( $to_insert->fields() ) {
1016 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1017 $marc->insert_fields_ordered( ($field) );
1021 $xml = $marc->as_xml_record;
1022 $xml =~ s/^<\?.+?\?>$//mo;
1024 $xml =~ s/>\s+</></sgo;
1029 $$ LANGUAGE PLPERLU STABLE;
1031 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1035 -- First make sure the circ matrix is loaded and the circulations
1036 -- have been staged to the extent possible (but at the very least
1037 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1038 -- circ modifiers must also be in place.
1040 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1048 this_duration_rule INT;
1050 this_max_fine_rule INT;
1051 rcd config.rule_circ_duration%ROWTYPE;
1052 rrf config.rule_recurring_fine%ROWTYPE;
1053 rmf config.rule_max_fine%ROWTYPE;
1060 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1062 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1064 -- Fetch the correct rules for this circulation
1071 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1074 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1075 INTO circ_lib, target_copy, usr, is_renewal ;
1077 INTO this_duration_rule,
1081 recurring_fine_rule,
1083 FROM action.item_user_circ_test(
1089 SELECT INTO rcd * FROM config.rule_circ_duration
1090 WHERE id = this_duration_rule;
1091 SELECT INTO rrf * FROM config.rule_recurring_fine
1092 WHERE id = this_fine_rule;
1093 SELECT INTO rmf * FROM config.rule_max_fine
1094 WHERE id = this_max_fine_rule;
1096 -- Apply the rules to this circulation
1097 EXECUTE ('UPDATE ' || tablename || ' c
1099 duration_rule = rcd.name,
1100 recurring_fine_rule = rrf.name,
1101 max_fine_rule = rmf.name,
1102 duration = rcd.normal,
1103 recurring_fine = rrf.normal,
1106 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1109 renewal_remaining = rcd.max_renewals
1111 config.rule_circ_duration rcd,
1112 config.rule_recurring_fine rrf,
1113 config.rule_max_fine rmf,
1116 rcd.id = ' || this_duration_rule || ' AND
1117 rrf.id = ' || this_fine_rule || ' AND
1118 rmf.id = ' || this_max_fine_rule || ' AND
1119 ac.id = c.target_copy AND
1120 c.id = ' || circ || ';');
1122 -- Keep track of where we are in the process
1124 IF (n % 100 = 0) THEN
1125 RAISE INFO '%', n || ' of ' || n_circs
1126 || ' (' || (100*n/n_circs) || '%) circs updated.';
1134 $$ LANGUAGE plpgsql;
1136 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1140 -- First make sure the circ matrix is loaded and the circulations
1141 -- have been staged to the extent possible (but at the very least
1142 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1143 -- circ modifiers must also be in place.
1145 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1153 this_duration_rule INT;
1155 this_max_fine_rule INT;
1156 rcd config.rule_circ_duration%ROWTYPE;
1157 rrf config.rule_recurring_fine%ROWTYPE;
1158 rmf config.rule_max_fine%ROWTYPE;
1165 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1167 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1169 -- Fetch the correct rules for this circulation
1176 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1179 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1180 INTO circ_lib, target_copy, usr, is_renewal ;
1182 INTO this_duration_rule,
1188 FROM action.find_circ_matrix_matchpoint(
1194 SELECT INTO rcd * FROM config.rule_circ_duration
1195 WHERE id = this_duration_rule;
1196 SELECT INTO rrf * FROM config.rule_recurring_fine
1197 WHERE id = this_fine_rule;
1198 SELECT INTO rmf * FROM config.rule_max_fine
1199 WHERE id = this_max_fine_rule;
1201 -- Apply the rules to this circulation
1202 EXECUTE ('UPDATE ' || tablename || ' c
1204 duration_rule = rcd.name,
1205 recuring_fine_rule = rrf.name,
1206 max_fine_rule = rmf.name,
1207 duration = rcd.normal,
1208 recuring_fine = rrf.normal,
1211 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1214 renewal_remaining = rcd.max_renewals
1216 config.rule_circ_duration rcd,
1217 config.rule_recuring_fine rrf,
1218 config.rule_max_fine rmf,
1221 rcd.id = ' || this_duration_rule || ' AND
1222 rrf.id = ' || this_fine_rule || ' AND
1223 rmf.id = ' || this_max_fine_rule || ' AND
1224 ac.id = c.target_copy AND
1225 c.id = ' || circ || ';');
1227 -- Keep track of where we are in the process
1229 IF (n % 100 = 0) THEN
1230 RAISE INFO '%', n || ' of ' || n_circs
1231 || ' (' || (100*n/n_circs) || '%) circs updated.';
1239 $$ LANGUAGE plpgsql;
1241 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1245 -- First make sure the circ matrix is loaded and the circulations
1246 -- have been staged to the extent possible (but at the very least
1247 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1248 -- circ modifiers must also be in place.
1250 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1258 this_duration_rule INT;
1260 this_max_fine_rule INT;
1261 rcd config.rule_circ_duration%ROWTYPE;
1262 rrf config.rule_recurring_fine%ROWTYPE;
1263 rmf config.rule_max_fine%ROWTYPE;
1270 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1272 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1274 -- Fetch the correct rules for this circulation
1281 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1284 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1285 INTO circ_lib, target_copy, usr, is_renewal ;
1287 INTO this_duration_rule,
1290 (matchpoint).duration_rule,
1291 (matchpoint).recurring_fine_rule,
1292 (matchpoint).max_fine_rule
1293 FROM action.find_circ_matrix_matchpoint(
1299 SELECT INTO rcd * FROM config.rule_circ_duration
1300 WHERE id = this_duration_rule;
1301 SELECT INTO rrf * FROM config.rule_recurring_fine
1302 WHERE id = this_fine_rule;
1303 SELECT INTO rmf * FROM config.rule_max_fine
1304 WHERE id = this_max_fine_rule;
1306 -- Apply the rules to this circulation
1307 EXECUTE ('UPDATE ' || tablename || ' c
1309 duration_rule = rcd.name,
1310 recurring_fine_rule = rrf.name,
1311 max_fine_rule = rmf.name,
1312 duration = rcd.normal,
1313 recurring_fine = rrf.normal,
1316 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1319 renewal_remaining = rcd.max_renewals,
1320 grace_period = rrf.grace_period
1322 config.rule_circ_duration rcd,
1323 config.rule_recurring_fine rrf,
1324 config.rule_max_fine rmf,
1327 rcd.id = ' || this_duration_rule || ' AND
1328 rrf.id = ' || this_fine_rule || ' AND
1329 rmf.id = ' || this_max_fine_rule || ' AND
1330 ac.id = c.target_copy AND
1331 c.id = ' || circ || ';');
1333 -- Keep track of where we are in the process
1335 IF (n % 100 = 0) THEN
1336 RAISE INFO '%', n || ' of ' || n_circs
1337 || ' (' || (100*n/n_circs) || '%) circs updated.';
1345 $$ LANGUAGE plpgsql;
1347 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1351 -- First make sure the circ matrix is loaded and the circulations
1352 -- have been staged to the extent possible (but at the very least
1353 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1354 -- circ modifiers must also be in place.
1356 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1364 this_duration_rule INT;
1366 this_max_fine_rule INT;
1367 rcd config.rule_circ_duration%ROWTYPE;
1368 rrf config.rule_recurring_fine%ROWTYPE;
1369 rmf config.rule_max_fine%ROWTYPE;
1375 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1377 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1379 -- Fetch the correct rules for this circulation
1386 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1389 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1390 INTO circ_lib, target_copy, usr, is_renewal ;
1392 INTO this_duration_rule,
1395 (matchpoint).duration_rule,
1396 (matchpoint).recurring_fine_rule,
1397 (matchpoint).max_fine_rule
1398 FROM action.find_circ_matrix_matchpoint(
1404 SELECT INTO rcd * FROM config.rule_circ_duration
1405 WHERE id = this_duration_rule;
1406 SELECT INTO rrf * FROM config.rule_recurring_fine
1407 WHERE id = this_fine_rule;
1408 SELECT INTO rmf * FROM config.rule_max_fine
1409 WHERE id = this_max_fine_rule;
1411 -- Apply the rules to this circulation
1412 EXECUTE ('UPDATE ' || tablename || ' c
1414 duration_rule = rcd.name,
1415 recurring_fine_rule = rrf.name,
1416 max_fine_rule = rmf.name,
1417 duration = rcd.normal,
1418 recurring_fine = rrf.normal,
1421 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1424 renewal_remaining = rcd.max_renewals,
1425 grace_period = rrf.grace_period
1427 config.rule_circ_duration rcd,
1428 config.rule_recurring_fine rrf,
1429 config.rule_max_fine rmf,
1432 rcd.id = ' || this_duration_rule || ' AND
1433 rrf.id = ' || this_fine_rule || ' AND
1434 rmf.id = ' || this_max_fine_rule || ' AND
1435 ac.id = c.target_copy AND
1436 c.id = ' || circ || ';');
1438 -- Keep track of where we are in the process
1440 IF (n % 100 = 0) THEN
1441 RAISE INFO '%', n || ' of ' || n_circs
1442 || ' (' || (100*n/n_circs) || '%) circs updated.';
1450 $$ LANGUAGE plpgsql;
1455 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1457 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1458 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1460 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1461 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1464 c TEXT := schemaname || '.asset_copy_legacy';
1465 sc TEXT := schemaname || '.asset_stat_cat';
1466 sce TEXT := schemaname || '.asset_stat_cat_entry';
1467 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1473 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1475 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1477 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1478 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1479 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1486 $$ LANGUAGE plpgsql;
1488 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1490 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1491 -- This will assign standing penalties as needed.
1499 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1501 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1503 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1513 $$ LANGUAGE plpgsql;
1516 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1519 INSERT INTO metabib.metarecord (fingerprint, master_record)
1520 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1521 FROM biblio.record_entry b
1523 AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
1524 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1525 ORDER BY b.fingerprint, b.quality DESC;
1526 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1528 FROM biblio.record_entry r
1529 JOIN metabib.metarecord m USING (fingerprint)
1530 WHERE NOT r.deleted;
1533 $$ LANGUAGE plpgsql;
1536 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1539 INSERT INTO metabib.metarecord (fingerprint, master_record)
1540 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1541 FROM biblio.record_entry b
1543 AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
1544 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1545 ORDER BY b.fingerprint, b.quality DESC;
1546 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1548 FROM biblio.record_entry r
1549 JOIN metabib.metarecord m USING (fingerprint)
1551 AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL);
1554 $$ LANGUAGE plpgsql;
1557 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1559 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1560 -- Then SELECT migration_tools.create_cards('m_foo');
1563 u TEXT := schemaname || '.actor_usr_legacy';
1564 c TEXT := schemaname || '.actor_card';
1568 EXECUTE ('DELETE FROM ' || c || ';');
1569 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1570 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1576 $$ LANGUAGE plpgsql;
1579 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1581 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1583 my ($marcxml, $shortname) = @_;
1586 use MARC::File::XML;
1591 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1593 foreach my $field ( $marc->field('856') ) {
1594 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1595 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1596 $field->add_subfields( '9' => $shortname );
1597 $field->update( ind2 => '0');
1601 $xml = $marc->as_xml_record;
1602 $xml =~ s/^<\?.+?\?>$//mo;
1604 $xml =~ s/>\s+</></sgo;
1609 $$ LANGUAGE PLPERLU STABLE;
1611 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1613 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1615 my ($marcxml, $shortname) = @_;
1618 use MARC::File::XML;
1623 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1625 foreach my $field ( $marc->field('856') ) {
1626 if ( ! $field->as_string('9') ) {
1627 $field->add_subfields( '9' => $shortname );
1631 $xml = $marc->as_xml_record;
1632 $xml =~ s/^<\?.+?\?>$//mo;
1634 $xml =~ s/>\s+</></sgo;
1639 $$ LANGUAGE PLPERLU STABLE;
1642 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1654 -- Bail out if asked to change the label to ##URI##
1655 IF new_label = '##URI##' THEN
1659 -- Gather information
1660 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1661 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1662 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1664 -- Bail out if the label already is ##URI##
1665 IF old_label = '##URI##' THEN
1669 -- Bail out if the call number label is already correct
1670 IF new_volume = old_volume THEN
1674 -- Check whether we already have a destination volume available
1675 SELECT id INTO new_volume FROM asset.call_number
1678 owning_lib = owner AND
1679 label = new_label AND
1682 -- Create destination volume if needed
1684 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1685 VALUES (1, 1, bib, owner, new_label, cn_class);
1686 SELECT id INTO new_volume FROM asset.call_number
1689 owning_lib = owner AND
1690 label = new_label AND
1694 -- Move copy to destination
1695 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1697 -- Delete source volume if it is now empty
1698 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1700 DELETE FROM asset.call_number WHERE id = old_volume;
1705 $$ LANGUAGE plpgsql;
1707 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1712 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1716 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1717 $zipdata{$zip} = [$city, $state, $county];
1720 if (defined $zipdata{$input}) {
1721 my ($city, $state, $county) = @{$zipdata{$input}};
1722 return [$city, $state, $county];
1723 } elsif (defined $zipdata{substr $input, 0, 5}) {
1724 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1725 return [$city, $state, $county];
1727 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1730 $$ LANGUAGE PLPERLU STABLE;
1732 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1739 errors_found BOOLEAN;
1741 parent_shortname TEXT;
1747 type_parent_depth INT;
1752 errors_found := FALSE;
1754 -- Checking actor.org_unit_type
1756 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1758 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1759 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1761 IF type_parent IS NOT NULL THEN
1763 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1765 IF type_depth - type_parent_depth <> 1 THEN
1766 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1767 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1768 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1769 ou_type_name, type_depth, parent_type, type_parent_depth;
1770 errors_found := TRUE;
1778 -- Checking actor.org_unit
1780 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1782 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1783 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
1784 SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
1785 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1786 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1787 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
1788 SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
1790 IF ou_parent IS NOT NULL THEN
1792 IF (org_unit_depth - parent_depth <> 1) OR (
1793 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1795 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1796 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1797 errors_found := TRUE;
1804 IF NOT errors_found THEN
1805 RAISE INFO 'No errors found.';
1812 $$ LANGUAGE plpgsql;
1815 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1819 DELETE FROM asset.opac_visible_copies;
1821 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1823 cp.id, cp.circ_lib, cn.record
1826 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1827 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1828 JOIN asset.copy_location cl ON (cp.location = cl.id)
1829 JOIN config.copy_status cs ON (cp.status = cs.id)
1830 JOIN biblio.record_entry b ON (cn.record = b.id)
1839 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1843 $$ LANGUAGE plpgsql;
1846 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1852 old_owning_lib INTEGER;
1858 -- Gather information
1859 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1860 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1861 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1863 -- Bail out if the new_owning_lib is not the ID of an org_unit
1864 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1866 '% is not a valid actor.org_unit ID; no change made.',
1871 -- Bail out discreetly if the owning_lib is already correct
1872 IF new_owning_lib = old_owning_lib THEN
1876 -- Check whether we already have a destination volume available
1877 SELECT id INTO new_volume FROM asset.call_number
1880 owning_lib = new_owning_lib AND
1881 label = old_label AND
1884 -- Create destination volume if needed
1886 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1887 VALUES (1, 1, bib, new_owning_lib, old_label);
1888 SELECT id INTO new_volume FROM asset.call_number
1891 owning_lib = new_owning_lib AND
1892 label = old_label AND
1896 -- Move copy to destination
1897 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1899 -- Delete source volume if it is now empty
1900 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1902 DELETE FROM asset.call_number WHERE id = old_volume;
1907 $$ LANGUAGE plpgsql;
1910 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1912 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1915 new_owning_lib INTEGER;
1919 -- Parse the new_owner as an org unit ID or shortname
1920 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1921 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1922 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1923 ELSIF new_owner ~ E'^[0-9]+$' THEN
1924 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1927 E'You don\'t need to put the actor.org_unit ID in quotes; '
1928 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1929 new_owning_lib := new_owner::INTEGER;
1930 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1934 '% is not a valid actor.org_unit shortname or ID; no change made.',
1941 $$ LANGUAGE plpgsql;
1943 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1946 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1949 MARC::Charset->assume_unicode(1);
1954 my $r = MARC::Record->new_from_xml( $xml );
1955 my $output_xml = $r->as_xml_record();
1963 $func$ LANGUAGE PLPERLU;
1964 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1966 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1968 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1969 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1970 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
1971 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1972 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1973 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
1974 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1975 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1976 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
1977 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1978 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1979 $$) TO '$$ || dir || $$/asset_copy_location'$$;
1980 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1981 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1982 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1983 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1984 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1985 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
1986 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1987 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1988 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
1989 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
1990 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
1991 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
1992 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
1993 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
1995 $FUNC$ LANGUAGE PLPGSQL;
1997 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1999 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
2000 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
2001 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
2002 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
2003 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
2004 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
2005 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
2007 -- import any new circ rules
2008 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2009 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2010 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2011 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2013 -- and permission groups
2014 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2017 $FUNC$ LANGUAGE PLPGSQL;
2020 CREATE OR REPLACE FUNCTION migration_tools.simple_import_new_rows_by_value(dir TEXT, schemaname TEXT, tablename TEXT, idcol TEXT, matchcol TEXT) RETURNS VOID AS $FUNC$
2029 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2030 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2031 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
2032 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2033 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2034 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2035 FOR name IN EXECUTE loopq LOOP
2036 EXECUTE existsq INTO ct USING name;
2038 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2039 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
2040 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2041 EXECUTE copyst USING name;
2045 $FUNC$ LANGUAGE PLPGSQL;
2047 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2053 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2056 MARC::Charset->assume_unicode(1);
2058 my $target_xml = shift;
2059 my $source_xml = shift;
2065 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2069 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2074 my $source_id = $source->subfield('901', 'c');
2075 $source_id = $source->subfield('903', 'a') unless $source_id;
2076 my $target_id = $target->subfield('901', 'c');
2077 $target_id = $target->subfield('903', 'a') unless $target_id;
2079 my %existing_fields;
2080 foreach my $tag (@$tags) {
2081 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2082 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2083 $target->insert_fields_ordered(map { $_->clone() } @to_add);
2085 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2089 my $xml = $target->as_xml_record;
2090 $xml =~ s/^<\?.+?\?>$//mo;
2092 $xml =~ s/>\s+</></sgo;
2096 $func$ LANGUAGE PLPERLU;
2097 COMMENT ON FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) IS 'Given two MARCXML strings and an array of tags, returns MARCXML representing the merge of the specified fields from the second MARCXML record into the first.';
2099 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2105 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2108 my $in_tags = shift;
2109 my $in_values = shift;
2111 # hack-and-slash parsing of array-passed-as-string;
2112 # this can go away once everybody is running Postgres 9.1+
2113 my $csv = Text::CSV->new({binary => 1});
2116 my $status = $csv->parse($in_tags);
2117 my $tags = [ $csv->fields() ];
2118 $in_values =~ s/^{//;
2119 $in_values =~ s/}$//;
2120 $status = $csv->parse($in_values);
2121 my $values = [ $csv->fields() ];
2123 my $marc = MARC::Record->new();
2125 $marc->leader('00000nam a22000007 4500');
2126 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2128 foreach my $i (0..$#$tags) {
2130 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2133 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2134 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2136 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2140 my $xml = $marc->as_xml_record;
2141 $xml =~ s/^<\?.+?\?>$//mo;
2143 $xml =~ s/>\s+</></sgo;
2147 $func$ LANGUAGE PLPERLU;
2148 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2149 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2150 The second argument is an array of text containing the values to plug into each field.
2151 If the value for a given field is NULL or the empty string, it is not inserted.
2154 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2156 my ($marcxml, $tag, $pos, $value) = @_;
2159 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2163 MARC::Charset->assume_unicode(1);
2165 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2166 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2167 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2168 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2172 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2174 foreach my $field ($marc->field($tag)) {
2175 $field->update("ind$pos" => $value);
2177 $xml = $marc->as_xml_record;
2178 $xml =~ s/^<\?.+?\?>$//mo;
2180 $xml =~ s/>\s+</></sgo;
2184 $func$ LANGUAGE PLPERLU;
2186 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2187 The first argument is a MARCXML string.
2188 The second argument is a MARC tag.
2189 The third argument is the indicator position, either 1 or 2.
2190 The fourth argument is the character to set the indicator value to.
2191 All occurences of the specified field will be changed.
2192 The function returns the revised MARCXML string.$$;
2194 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2199 first_name TEXT DEFAULT '',
2200 last_name TEXT DEFAULT ''
2201 ) RETURNS VOID AS $func$
2203 RAISE NOTICE '%', org ;
2204 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2205 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2206 FROM actor.org_unit aou, permission.grp_tree pgt
2207 WHERE aou.shortname = org
2208 AND pgt.name = perm_group;
2213 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2214 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2216 target_event_def ALIAS FOR $1;
2219 DROP TABLE IF EXISTS new_atevdefs;
2220 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2221 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2222 INSERT INTO action_trigger.event_definition (
2243 ,name || ' (clone of '||target_event_def||')'
2259 action_trigger.event_definition
2261 id = target_event_def
2263 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2264 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2265 INSERT INTO action_trigger.environment (
2271 currval('action_trigger.event_definition_id_seq')
2276 action_trigger.environment
2278 event_def = target_event_def
2280 INSERT INTO action_trigger.event_params (
2285 currval('action_trigger.event_definition_id_seq')
2289 action_trigger.event_params
2291 event_def = target_event_def
2294 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2296 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2298 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2299 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2301 target_event_def ALIAS FOR $1;
2303 new_interval ALIAS FOR $3;
2305 DROP TABLE IF EXISTS new_atevdefs;
2306 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2307 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2308 INSERT INTO action_trigger.event_definition (
2329 ,name || ' (clone of '||target_event_def||')'
2345 action_trigger.event_definition
2347 id = target_event_def
2349 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2350 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2351 INSERT INTO action_trigger.environment (
2357 currval('action_trigger.event_definition_id_seq')
2362 action_trigger.environment
2364 event_def = target_event_def
2366 INSERT INTO action_trigger.event_params (
2371 currval('action_trigger.event_definition_id_seq')
2375 action_trigger.event_params
2377 event_def = target_event_def
2380 RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2382 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2384 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2385 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2388 use MARC::File::XML;
2393 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2394 $field = $marc->field($tag);
2396 return $field->as_string($subfield,$delimiter);
2397 $$ LANGUAGE PLPERLU STABLE;
2399 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2400 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2403 use MARC::File::XML;
2408 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2409 @fields = $marc->field($tag);
2412 foreach my $field (@fields) {
2413 push @texts, $field->as_string($subfield,$delimiter);
2416 $$ LANGUAGE PLPERLU STABLE;
2418 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2419 SELECT action.find_hold_matrix_matchpoint(
2420 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2421 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2422 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2423 (SELECT usr FROM action.hold_request WHERE id = $1),
2424 (SELECT requestor FROM action.hold_request WHERE id = $1)
2428 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2429 SELECT action.hold_request_permit_test(
2430 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2431 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2432 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2433 (SELECT usr FROM action.hold_request WHERE id = $1),
2434 (SELECT requestor FROM action.hold_request WHERE id = $1)
2438 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2439 SELECT action.find_circ_matrix_matchpoint(
2440 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2441 (SELECT target_copy FROM action.circulation WHERE id = $1),
2442 (SELECT usr FROM action.circulation WHERE id = $1),
2444 NULLIF(phone_renewal,false),
2445 NULLIF(desk_renewal,false),
2446 NULLIF(opac_renewal,false),
2448 ) FROM action.circulation WHERE id = $1
2453 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2458 RAISE EXCEPTION 'assertion';
2461 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2463 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2469 RAISE EXCEPTION '%', msg;
2472 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2474 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2477 fail_msg ALIAS FOR $2;
2478 success_msg ALIAS FOR $3;
2481 RAISE EXCEPTION '%', fail_msg;
2485 $$ LANGUAGE PLPGSQL STRICT VOLATILE;