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,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.fund,acq.fund_allocation,acq.fund_tag,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 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.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
357 city_state_zip TEXT := $1;
362 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;
363 city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
364 IF city_state_zip ~ ',' THEN
365 state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
366 city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
368 IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
369 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
370 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
372 IF city_state_zip ~ E'^\\S+$' THEN
373 city := city_state_zip;
376 state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
377 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
381 RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
383 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
385 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
389 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
390 IF o::BIGINT < t THEN
397 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
399 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
401 migration_schema ALIAS FOR $1;
405 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
410 $$ LANGUAGE PLPGSQL STRICT STABLE;
412 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
414 migration_schema ALIAS FOR $1;
418 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
423 $$ LANGUAGE PLPGSQL STRICT STABLE;
425 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
427 migration_schema ALIAS FOR $1;
431 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
436 $$ LANGUAGE PLPGSQL STRICT STABLE;
438 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
440 migration_schema ALIAS FOR $1;
444 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
449 $$ LANGUAGE PLPGSQL STRICT STABLE;
451 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
453 migration_schema ALIAS FOR $1;
455 patron_table ALIAS FOR $2;
456 default_patron_profile ALIAS FOR $3;
459 sql_where1 TEXT := '';
460 sql_where2 TEXT := '';
461 sql_where3 TEXT := '';
464 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
466 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
468 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
469 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);
470 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);
471 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);
472 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,'') || ';';
473 --RAISE INFO 'sql = %', sql;
474 PERFORM migration_tools.exec( $1, sql );
476 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
478 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
480 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
483 $$ LANGUAGE PLPGSQL STRICT STABLE;
485 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
487 migration_schema ALIAS FOR $1;
489 item_table ALIAS FOR $2;
492 sql_where1 TEXT := '';
493 sql_where2 TEXT := '';
494 sql_where3 TEXT := '';
497 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
499 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
501 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 ';
502 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);
503 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);
504 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);
505 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,'') || ';';
506 --RAISE INFO 'sql = %', sql;
507 PERFORM migration_tools.exec( $1, sql );
510 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
512 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
515 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
517 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
519 migration_schema ALIAS FOR $1;
520 base_copy_location_map TEXT;
521 item_table ALIAS FOR $2;
524 sql_where1 TEXT := '';
525 sql_where2 TEXT := '';
526 sql_where3 TEXT := '';
529 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
531 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
533 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
534 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);
535 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);
536 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);
537 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,'') || ';';
538 --RAISE INFO 'sql = %', sql;
539 PERFORM migration_tools.exec( $1, sql );
542 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
544 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
547 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
549 -- 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
550 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
552 migration_schema ALIAS FOR $1;
554 circ_table ALIAS FOR $2;
555 item_table ALIAS FOR $3;
556 patron_table ALIAS FOR $4;
559 sql_where1 TEXT := '';
560 sql_where2 TEXT := '';
561 sql_where3 TEXT := '';
562 sql_where4 TEXT := '';
565 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
567 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
569 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 ';
570 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);
571 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);
572 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);
573 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);
574 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,'') || ';';
575 --RAISE INFO 'sql = %', sql;
576 PERFORM migration_tools.exec( $1, sql );
579 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
581 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
584 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
587 -- $barcode source barcode
588 -- $prefix prefix to add to barcode, NULL = add no prefix
589 -- $maxlen maximum length of barcode; default to 14 if left NULL
590 -- $pad padding string to apply to left of source barcode before adding
591 -- prefix and suffix; set to NULL or '' if no padding is desired
592 -- $suffix suffix to add to barcode, NULL = add no suffix
594 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
595 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
597 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
598 my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
601 return unless defined $barcode;
603 $prefix = '' unless defined $prefix;
605 $pad = '0' unless defined $pad;
606 $suffix = '' unless defined $suffix;
608 # bail out if adding prefix and suffix would bring new barcode over max length
609 return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
611 my $new_barcode = $barcode;
613 my $pad_length = $maxlen - length($prefix) - length($suffix);
614 if (length($barcode) < $pad_length) {
615 # assuming we always want padding on the left
616 # also assuming that it is possible to have the pad string be longer than 1 character
617 $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
621 # bail out if adding prefix and suffix would bring new barcode over max length
622 return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
624 return "$prefix$new_barcode$suffix";
625 $$ LANGUAGE PLPERLU STABLE;
627 -- remove previous version of this function
628 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
630 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
632 attempt_value ALIAS FOR $1;
633 datatype ALIAS FOR $2;
635 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
636 RETURN attempt_value;
638 WHEN OTHERS THEN RETURN NULL;
640 $$ LANGUAGE PLPGSQL STRICT STABLE;
642 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
644 attempt_value ALIAS FOR $1;
645 fail_value ALIAS FOR $2;
649 EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
656 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
661 $$ LANGUAGE PLPGSQL STRICT STABLE;
663 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
665 attempt_value ALIAS FOR $1;
666 fail_value ALIAS FOR $2;
670 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
677 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
682 $$ LANGUAGE PLPGSQL STRICT STABLE;
684 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
686 attempt_value ALIAS FOR $1;
687 fail_value ALIAS FOR $2;
691 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
698 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
703 $$ LANGUAGE PLPGSQL STRICT STABLE;
705 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
707 attempt_value ALIAS FOR $1;
708 fail_value ALIAS FOR $2;
712 EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
719 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
724 $$ LANGUAGE PLPGSQL STRICT STABLE;
726 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
728 attempt_value ALIAS FOR $1;
729 fail_value ALIAS FOR $2;
732 IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
733 RAISE EXCEPTION 'too many digits';
736 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;'
743 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
748 $$ LANGUAGE PLPGSQL STRICT STABLE;
750 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
752 attempt_value ALIAS FOR $1;
753 fail_value ALIAS FOR $2;
756 IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
757 RAISE EXCEPTION 'too many digits';
760 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;'
767 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
772 $$ LANGUAGE PLPGSQL STRICT STABLE;
774 -- add_codabar_checkdigit
775 -- $barcode source barcode
777 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
778 -- character with a checkdigit computed according to the usual algorithm for library barcodes
779 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
780 -- input string does not meet those requirements, it is returned unchanged.
782 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
785 return $barcode if $barcode !~ /^\d{13,14}$/;
786 $barcode = substr($barcode, 0, 13); # ignore 14th digit
787 my @digits = split //, $barcode;
789 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
790 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
791 my $remainder = $total % 10;
792 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
793 return $barcode . $checkdigit;
794 $$ LANGUAGE PLPERLU STRICT STABLE;
796 -- add_code39mod43_checkdigit
797 -- $barcode source barcode
799 -- If the source string is 13 or 14 characters long and contains only valid
800 -- Code 39 mod 43 characters, adds or replaces the 14th
801 -- character with a checkdigit computed according to the usual algorithm for library barcodes
802 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>. If the
803 -- input string does not meet those requirements, it is returned unchanged.
805 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
808 return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
809 $barcode = substr($barcode, 0, 13); # ignore 14th character
811 my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
812 my %nums = map { $valid_chars[$_] => $_ } (0..42);
815 $total += $nums{$_} foreach split(//, $barcode);
816 my $remainder = $total % 43;
817 my $checkdigit = $valid_chars[$remainder];
818 return $barcode . $checkdigit;
819 $$ LANGUAGE PLPERLU STRICT STABLE;
821 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
827 n_digits INTEGER := 0;
830 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
831 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
832 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
833 IF n_digits = 7 AND areacode <> '' THEN
834 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
835 output := (areacode || '-' || temp);
842 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
844 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
845 my ($marcxml, $pos, $value) = @_;
852 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
853 my $leader = $marc->leader();
854 substr($leader, $pos, 1) = $value;
855 $marc->leader($leader);
856 $xml = $marc->as_xml_record;
857 $xml =~ s/^<\?.+?\?>$//mo;
859 $xml =~ s/>\s+</></sgo;
862 $$ LANGUAGE PLPERLU STABLE;
864 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
865 my ($marcxml, $pos, $value) = @_;
872 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
873 my $f008 = $marc->field('008');
876 my $field = $f008->data();
877 substr($field, $pos, 1) = $value;
878 $f008->update($field);
879 $xml = $marc->as_xml_record;
880 $xml =~ s/^<\?.+?\?>$//mo;
882 $xml =~ s/>\s+</></sgo;
886 $$ LANGUAGE PLPERLU STABLE;
889 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
891 profile ALIAS FOR $1;
893 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
895 $$ LANGUAGE PLPGSQL STRICT STABLE;
898 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
900 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
902 $$ LANGUAGE PLPGSQL STRICT STABLE;
905 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
907 my ($marcxml, $tags) = @_;
915 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
916 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
920 foreach my $field ( $marc->fields() ) {
921 push @incumbents, $field->as_formatted();
924 foreach $field ( $to_insert->fields() ) {
925 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
926 $marc->insert_fields_ordered( ($field) );
930 $xml = $marc->as_xml_record;
931 $xml =~ s/^<\?.+?\?>$//mo;
933 $xml =~ s/>\s+</></sgo;
938 $$ LANGUAGE PLPERLU STABLE;
940 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
944 -- First make sure the circ matrix is loaded and the circulations
945 -- have been staged to the extent possible (but at the very least
946 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
947 -- circ modifiers must also be in place.
949 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
957 this_duration_rule INT;
959 this_max_fine_rule INT;
960 rcd config.rule_circ_duration%ROWTYPE;
961 rrf config.rule_recurring_fine%ROWTYPE;
962 rmf config.rule_max_fine%ROWTYPE;
969 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
971 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
973 -- Fetch the correct rules for this circulation
980 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
983 FROM ' || tablename || ' WHERE id = ' || circ || ';')
984 INTO circ_lib, target_copy, usr, is_renewal ;
986 INTO this_duration_rule,
992 FROM action.item_user_circ_test(
998 SELECT INTO rcd * FROM config.rule_circ_duration
999 WHERE id = this_duration_rule;
1000 SELECT INTO rrf * FROM config.rule_recurring_fine
1001 WHERE id = this_fine_rule;
1002 SELECT INTO rmf * FROM config.rule_max_fine
1003 WHERE id = this_max_fine_rule;
1005 -- Apply the rules to this circulation
1006 EXECUTE ('UPDATE ' || tablename || ' c
1008 duration_rule = rcd.name,
1009 recurring_fine_rule = rrf.name,
1010 max_fine_rule = rmf.name,
1011 duration = rcd.normal,
1012 recurring_fine = rrf.normal,
1015 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1018 renewal_remaining = rcd.max_renewals
1020 config.rule_circ_duration rcd,
1021 config.rule_recurring_fine rrf,
1022 config.rule_max_fine rmf,
1025 rcd.id = ' || this_duration_rule || ' AND
1026 rrf.id = ' || this_fine_rule || ' AND
1027 rmf.id = ' || this_max_fine_rule || ' AND
1028 ac.id = c.target_copy AND
1029 c.id = ' || circ || ';');
1031 -- Keep track of where we are in the process
1033 IF (n % 100 = 0) THEN
1034 RAISE INFO '%', n || ' of ' || n_circs
1035 || ' (' || (100*n/n_circs) || '%) circs updated.';
1043 $$ LANGUAGE plpgsql;
1045 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1049 -- First make sure the circ matrix is loaded and the circulations
1050 -- have been staged to the extent possible (but at the very least
1051 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1052 -- circ modifiers must also be in place.
1054 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1062 this_duration_rule INT;
1064 this_max_fine_rule INT;
1065 rcd config.rule_circ_duration%ROWTYPE;
1066 rrf config.rule_recurring_fine%ROWTYPE;
1067 rmf config.rule_max_fine%ROWTYPE;
1074 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1076 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1078 -- Fetch the correct rules for this circulation
1085 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1088 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1089 INTO circ_lib, target_copy, usr, is_renewal ;
1091 INTO this_duration_rule,
1097 FROM action.find_circ_matrix_matchpoint(
1103 SELECT INTO rcd * FROM config.rule_circ_duration
1104 WHERE id = this_duration_rule;
1105 SELECT INTO rrf * FROM config.rule_recurring_fine
1106 WHERE id = this_fine_rule;
1107 SELECT INTO rmf * FROM config.rule_max_fine
1108 WHERE id = this_max_fine_rule;
1110 -- Apply the rules to this circulation
1111 EXECUTE ('UPDATE ' || tablename || ' c
1113 duration_rule = rcd.name,
1114 recuring_fine_rule = rrf.name,
1115 max_fine_rule = rmf.name,
1116 duration = rcd.normal,
1117 recuring_fine = rrf.normal,
1120 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1123 renewal_remaining = rcd.max_renewals
1125 config.rule_circ_duration rcd,
1126 config.rule_recuring_fine rrf,
1127 config.rule_max_fine rmf,
1130 rcd.id = ' || this_duration_rule || ' AND
1131 rrf.id = ' || this_fine_rule || ' AND
1132 rmf.id = ' || this_max_fine_rule || ' AND
1133 ac.id = c.target_copy AND
1134 c.id = ' || circ || ';');
1136 -- Keep track of where we are in the process
1138 IF (n % 100 = 0) THEN
1139 RAISE INFO '%', n || ' of ' || n_circs
1140 || ' (' || (100*n/n_circs) || '%) circs updated.';
1148 $$ LANGUAGE plpgsql;
1150 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1154 -- First make sure the circ matrix is loaded and the circulations
1155 -- have been staged to the extent possible (but at the very least
1156 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1157 -- circ modifiers must also be in place.
1159 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1167 this_duration_rule INT;
1169 this_max_fine_rule INT;
1170 rcd config.rule_circ_duration%ROWTYPE;
1171 rrf config.rule_recurring_fine%ROWTYPE;
1172 rmf config.rule_max_fine%ROWTYPE;
1179 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1181 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1183 -- Fetch the correct rules for this circulation
1190 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1193 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1194 INTO circ_lib, target_copy, usr, is_renewal ;
1196 INTO this_duration_rule,
1199 (matchpoint).duration_rule,
1200 (matchpoint).recurring_fine_rule,
1201 (matchpoint).max_fine_rule
1202 FROM action.find_circ_matrix_matchpoint(
1208 SELECT INTO rcd * FROM config.rule_circ_duration
1209 WHERE id = this_duration_rule;
1210 SELECT INTO rrf * FROM config.rule_recurring_fine
1211 WHERE id = this_fine_rule;
1212 SELECT INTO rmf * FROM config.rule_max_fine
1213 WHERE id = this_max_fine_rule;
1215 -- Apply the rules to this circulation
1216 EXECUTE ('UPDATE ' || tablename || ' c
1218 duration_rule = rcd.name,
1219 recurring_fine_rule = rrf.name,
1220 max_fine_rule = rmf.name,
1221 duration = rcd.normal,
1222 recurring_fine = rrf.normal,
1225 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1228 renewal_remaining = rcd.max_renewals,
1229 grace_period = rrf.grace_period
1231 config.rule_circ_duration rcd,
1232 config.rule_recurring_fine rrf,
1233 config.rule_max_fine rmf,
1236 rcd.id = ' || this_duration_rule || ' AND
1237 rrf.id = ' || this_fine_rule || ' AND
1238 rmf.id = ' || this_max_fine_rule || ' AND
1239 ac.id = c.target_copy AND
1240 c.id = ' || circ || ';');
1242 -- Keep track of where we are in the process
1244 IF (n % 100 = 0) THEN
1245 RAISE INFO '%', n || ' of ' || n_circs
1246 || ' (' || (100*n/n_circs) || '%) circs updated.';
1254 $$ LANGUAGE plpgsql;
1256 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1260 -- First make sure the circ matrix is loaded and the circulations
1261 -- have been staged to the extent possible (but at the very least
1262 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1263 -- circ modifiers must also be in place.
1265 -- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1273 this_duration_rule INT;
1275 this_max_fine_rule INT;
1276 rcd config.rule_circ_duration%ROWTYPE;
1277 rrf config.rule_recurring_fine%ROWTYPE;
1278 rmf config.rule_max_fine%ROWTYPE;
1284 --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1286 --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1288 -- Fetch the correct rules for this circulation
1295 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1298 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1299 INTO circ_lib, target_copy, usr, is_renewal ;
1301 INTO this_duration_rule,
1304 (matchpoint).duration_rule,
1305 (matchpoint).recurring_fine_rule,
1306 (matchpoint).max_fine_rule
1307 FROM action.find_circ_matrix_matchpoint(
1313 SELECT INTO rcd * FROM config.rule_circ_duration
1314 WHERE id = this_duration_rule;
1315 SELECT INTO rrf * FROM config.rule_recurring_fine
1316 WHERE id = this_fine_rule;
1317 SELECT INTO rmf * FROM config.rule_max_fine
1318 WHERE id = this_max_fine_rule;
1320 -- Apply the rules to this circulation
1321 EXECUTE ('UPDATE ' || tablename || ' c
1323 duration_rule = rcd.name,
1324 recurring_fine_rule = rrf.name,
1325 max_fine_rule = rmf.name,
1326 duration = rcd.normal,
1327 recurring_fine = rrf.normal,
1330 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1333 renewal_remaining = rcd.max_renewals,
1334 grace_period = rrf.grace_period
1336 config.rule_circ_duration rcd,
1337 config.rule_recurring_fine rrf,
1338 config.rule_max_fine rmf,
1341 rcd.id = ' || this_duration_rule || ' AND
1342 rrf.id = ' || this_fine_rule || ' AND
1343 rmf.id = ' || this_max_fine_rule || ' AND
1344 ac.id = c.target_copy AND
1345 c.id = ' || circ || ';');
1347 -- Keep track of where we are in the process
1349 IF (n % 100 = 0) THEN
1350 RAISE INFO '%', n || ' of ' || n_circs
1351 || ' (' || (100*n/n_circs) || '%) circs updated.';
1359 $$ LANGUAGE plpgsql;
1364 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1366 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1367 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1369 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1370 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1373 c TEXT := schemaname || '.asset_copy_legacy';
1374 sc TEXT := schemaname || '.asset_stat_cat';
1375 sce TEXT := schemaname || '.asset_stat_cat_entry';
1376 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1382 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1384 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1386 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1387 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1388 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1395 $$ LANGUAGE plpgsql;
1397 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1399 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1400 -- This will assign standing penalties as needed.
1408 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1410 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1412 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1422 $$ LANGUAGE plpgsql;
1425 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1428 INSERT INTO metabib.metarecord (fingerprint, master_record)
1429 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1430 FROM biblio.record_entry b
1432 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)
1433 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1434 ORDER BY b.fingerprint, b.quality DESC;
1435 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1437 FROM biblio.record_entry r
1438 JOIN metabib.metarecord m USING (fingerprint)
1439 WHERE NOT r.deleted;
1442 $$ LANGUAGE plpgsql;
1445 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1448 INSERT INTO metabib.metarecord (fingerprint, master_record)
1449 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1450 FROM biblio.record_entry b
1452 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)
1453 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1454 ORDER BY b.fingerprint, b.quality DESC;
1455 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1457 FROM biblio.record_entry r
1458 JOIN metabib.metarecord m USING (fingerprint)
1460 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);
1463 $$ LANGUAGE plpgsql;
1466 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1468 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1469 -- Then SELECT migration_tools.create_cards('m_foo');
1472 u TEXT := schemaname || '.actor_usr_legacy';
1473 c TEXT := schemaname || '.actor_card';
1477 EXECUTE ('DELETE FROM ' || c || ';');
1478 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1479 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1485 $$ LANGUAGE plpgsql;
1488 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1490 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1492 my ($marcxml, $shortname) = @_;
1495 use MARC::File::XML;
1500 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1502 foreach my $field ( $marc->field('856') ) {
1503 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1504 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1505 $field->add_subfields( '9' => $shortname );
1506 $field->update( ind2 => '0');
1510 $xml = $marc->as_xml_record;
1511 $xml =~ s/^<\?.+?\?>$//mo;
1513 $xml =~ s/>\s+</></sgo;
1518 $$ LANGUAGE PLPERLU STABLE;
1520 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1522 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1524 my ($marcxml, $shortname) = @_;
1527 use MARC::File::XML;
1532 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1534 foreach my $field ( $marc->field('856') ) {
1535 if ( ! $field->as_string('9') ) {
1536 $field->add_subfields( '9' => $shortname );
1540 $xml = $marc->as_xml_record;
1541 $xml =~ s/^<\?.+?\?>$//mo;
1543 $xml =~ s/>\s+</></sgo;
1548 $$ LANGUAGE PLPERLU STABLE;
1551 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1563 -- Bail out if asked to change the label to ##URI##
1564 IF new_label = '##URI##' THEN
1568 -- Gather information
1569 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1570 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1571 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1573 -- Bail out if the label already is ##URI##
1574 IF old_label = '##URI##' THEN
1578 -- Bail out if the call number label is already correct
1579 IF new_volume = old_volume THEN
1583 -- Check whether we already have a destination volume available
1584 SELECT id INTO new_volume FROM asset.call_number
1587 owning_lib = owner AND
1588 label = new_label AND
1591 -- Create destination volume if needed
1593 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1594 VALUES (1, 1, bib, owner, new_label, cn_class);
1595 SELECT id INTO new_volume FROM asset.call_number
1598 owning_lib = owner AND
1599 label = new_label AND
1603 -- Move copy to destination
1604 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1606 -- Delete source volume if it is now empty
1607 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1609 DELETE FROM asset.call_number WHERE id = old_volume;
1614 $$ LANGUAGE plpgsql;
1616 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1621 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1625 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1626 $zipdata{$zip} = [$city, $state, $county];
1629 if (defined $zipdata{$input}) {
1630 my ($city, $state, $county) = @{$zipdata{$input}};
1631 return [$city, $state, $county];
1632 } elsif (defined $zipdata{substr $input, 0, 5}) {
1633 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1634 return [$city, $state, $county];
1636 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1639 $$ LANGUAGE PLPERLU STABLE;
1641 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1648 errors_found BOOLEAN;
1650 parent_shortname TEXT;
1656 type_parent_depth INT;
1661 errors_found := FALSE;
1663 -- Checking actor.org_unit_type
1665 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1667 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1668 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1670 IF type_parent IS NOT NULL THEN
1672 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1674 IF type_depth - type_parent_depth <> 1 THEN
1675 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1676 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1677 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1678 ou_type_name, type_depth, parent_type, type_parent_depth;
1679 errors_found := TRUE;
1687 -- Checking actor.org_unit
1689 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1691 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1692 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;
1693 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;
1694 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1695 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1696 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;
1697 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;
1699 IF ou_parent IS NOT NULL THEN
1701 IF (org_unit_depth - parent_depth <> 1) OR (
1702 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1704 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1705 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1706 errors_found := TRUE;
1713 IF NOT errors_found THEN
1714 RAISE INFO 'No errors found.';
1721 $$ LANGUAGE plpgsql;
1724 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1728 DELETE FROM asset.opac_visible_copies;
1730 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1732 cp.id, cp.circ_lib, cn.record
1735 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1736 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1737 JOIN asset.copy_location cl ON (cp.location = cl.id)
1738 JOIN config.copy_status cs ON (cp.status = cs.id)
1739 JOIN biblio.record_entry b ON (cn.record = b.id)
1748 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1752 $$ LANGUAGE plpgsql;
1755 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1761 old_owning_lib INTEGER;
1767 -- Gather information
1768 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1769 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1770 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1772 -- Bail out if the new_owning_lib is not the ID of an org_unit
1773 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1775 '% is not a valid actor.org_unit ID; no change made.',
1780 -- Bail out discreetly if the owning_lib is already correct
1781 IF new_owning_lib = old_owning_lib THEN
1785 -- Check whether we already have a destination volume available
1786 SELECT id INTO new_volume FROM asset.call_number
1789 owning_lib = new_owning_lib AND
1790 label = old_label AND
1793 -- Create destination volume if needed
1795 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1796 VALUES (1, 1, bib, new_owning_lib, old_label);
1797 SELECT id INTO new_volume FROM asset.call_number
1800 owning_lib = new_owning_lib AND
1801 label = old_label AND
1805 -- Move copy to destination
1806 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1808 -- Delete source volume if it is now empty
1809 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1811 DELETE FROM asset.call_number WHERE id = old_volume;
1816 $$ LANGUAGE plpgsql;
1819 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1821 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1824 new_owning_lib INTEGER;
1828 -- Parse the new_owner as an org unit ID or shortname
1829 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1830 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1831 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1832 ELSIF new_owner ~ E'^[0-9]+$' THEN
1833 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1836 E'You don\'t need to put the actor.org_unit ID in quotes; '
1837 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1838 new_owning_lib := new_owner::INTEGER;
1839 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1843 '% is not a valid actor.org_unit shortname or ID; no change made.',
1850 $$ LANGUAGE plpgsql;
1852 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1855 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1858 MARC::Charset->assume_unicode(1);
1863 my $r = MARC::Record->new_from_xml( $xml );
1864 my $output_xml = $r->as_xml_record();
1872 $func$ LANGUAGE PLPERLU;
1873 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1875 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1877 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1878 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1879 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
1880 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1881 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1882 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
1883 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1884 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1885 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
1886 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1887 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1888 $$) TO '$$ || dir || $$/asset_copy_location'$$;
1889 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1890 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1891 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1892 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1893 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1894 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
1895 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1896 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1897 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
1898 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
1899 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
1900 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
1901 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
1902 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
1904 $FUNC$ LANGUAGE PLPGSQL;
1906 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1908 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
1909 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
1910 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
1911 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
1912 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1913 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
1914 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
1916 -- import any new circ rules
1917 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1918 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1919 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1920 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1922 -- and permission groups
1923 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1926 $FUNC$ LANGUAGE PLPGSQL;
1929 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$
1938 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
1939 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
1940 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
1941 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
1942 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
1943 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
1944 FOR name IN EXECUTE loopq LOOP
1945 EXECUTE existsq INTO ct USING name;
1947 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
1948 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
1949 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
1950 EXECUTE copyst USING name;
1954 $FUNC$ LANGUAGE PLPGSQL;
1956 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
1962 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1965 MARC::Charset->assume_unicode(1);
1967 my $target_xml = shift;
1968 my $source_xml = shift;
1974 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
1978 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
1983 my $source_id = $source->subfield('901', 'c');
1984 $source_id = $source->subfield('903', 'a') unless $source_id;
1985 my $target_id = $target->subfield('901', 'c');
1986 $target_id = $target->subfield('903', 'a') unless $target_id;
1988 my %existing_fields;
1989 foreach my $tag (@$tags) {
1990 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
1991 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
1992 $target->insert_fields_ordered(map { $_->clone() } @to_add);
1994 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
1998 my $xml = $target->as_xml_record;
1999 $xml =~ s/^<\?.+?\?>$//mo;
2001 $xml =~ s/>\s+</></sgo;
2005 $func$ LANGUAGE PLPERLU;
2006 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.';
2008 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2014 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2017 my $in_tags = shift;
2018 my $in_values = shift;
2020 # hack-and-slash parsing of array-passed-as-string;
2021 # this can go away once everybody is running Postgres 9.1+
2022 my $csv = Text::CSV->new({binary => 1});
2025 my $status = $csv->parse($in_tags);
2026 my $tags = [ $csv->fields() ];
2027 $in_values =~ s/^{//;
2028 $in_values =~ s/}$//;
2029 $status = $csv->parse($in_values);
2030 my $values = [ $csv->fields() ];
2032 my $marc = MARC::Record->new();
2034 $marc->leader('00000nam a22000007 4500');
2035 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
2037 foreach my $i (0..$#$tags) {
2039 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2042 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2043 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2045 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2049 my $xml = $marc->as_xml_record;
2050 $xml =~ s/^<\?.+?\?>$//mo;
2052 $xml =~ s/>\s+</></sgo;
2056 $func$ LANGUAGE PLPERLU;
2057 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2058 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2059 The second argument is an array of text containing the values to plug into each field.
2060 If the value for a given field is NULL or the empty string, it is not inserted.
2063 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2065 my ($marcxml, $tag, $pos, $value) = @_;
2068 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2072 MARC::Charset->assume_unicode(1);
2074 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2075 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2076 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2077 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2081 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2083 foreach my $field ($marc->field($tag)) {
2084 $field->update("ind$pos" => $value);
2086 $xml = $marc->as_xml_record;
2087 $xml =~ s/^<\?.+?\?>$//mo;
2089 $xml =~ s/>\s+</></sgo;
2093 $func$ LANGUAGE PLPERLU;
2095 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2096 The first argument is a MARCXML string.
2097 The second argument is a MARC tag.
2098 The third argument is the indicator position, either 1 or 2.
2099 The fourth argument is the character to set the indicator value to.
2100 All occurences of the specified field will be changed.
2101 The function returns the revised MARCXML string.$$;
2103 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2108 first_name TEXT DEFAULT '',
2109 last_name TEXT DEFAULT ''
2110 ) RETURNS VOID AS $func$
2112 RAISE NOTICE '%', org ;
2113 INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2114 SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2115 FROM actor.org_unit aou, permission.grp_tree pgt
2116 WHERE aou.shortname = org
2117 AND pgt.name = perm_group;
2122 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2123 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2125 target_event_def ALIAS FOR $1;
2128 DROP TABLE IF EXISTS new_atevdefs;
2129 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2130 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2131 INSERT INTO action_trigger.event_definition (
2152 ,name || ' (clone of '||target_event_def||')'
2168 action_trigger.event_definition
2170 id = target_event_def
2172 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2173 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2174 INSERT INTO action_trigger.environment (
2180 currval('action_trigger.event_definition_id_seq')
2185 action_trigger.environment
2187 event_def = target_event_def
2189 INSERT INTO action_trigger.event_params (
2194 currval('action_trigger.event_definition_id_seq')
2198 action_trigger.event_params
2200 event_def = target_event_def
2203 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);
2205 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2207 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2208 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2210 target_event_def ALIAS FOR $1;
2212 new_interval ALIAS FOR $3;
2214 DROP TABLE IF EXISTS new_atevdefs;
2215 CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2216 FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2217 INSERT INTO action_trigger.event_definition (
2238 ,name || ' (clone of '||target_event_def||')'
2254 action_trigger.event_definition
2256 id = target_event_def
2258 RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2259 INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2260 INSERT INTO action_trigger.environment (
2266 currval('action_trigger.event_definition_id_seq')
2271 action_trigger.environment
2273 event_def = target_event_def
2275 INSERT INTO action_trigger.event_params (
2280 currval('action_trigger.event_definition_id_seq')
2284 action_trigger.event_params
2286 event_def = target_event_def
2289 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);
2291 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2293 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2294 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2297 use MARC::File::XML;
2302 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2303 $field = $marc->field($tag);
2305 return $field->as_string($subfield,$delimiter);
2306 $$ LANGUAGE PLPERLU STABLE;
2308 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2309 my ($marcxml, $tag, $subfield, $delimiter) = @_;
2312 use MARC::File::XML;
2317 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2318 @fields = $marc->field($tag);
2321 foreach my $field (@fields) {
2322 push @texts, $field->as_string($subfield,$delimiter);
2325 $$ LANGUAGE PLPERLU STABLE;
2327 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2328 SELECT action.find_hold_matrix_matchpoint(
2329 (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2330 (SELECT request_lib FROM action.hold_request WHERE id = $1),
2331 (SELECT current_copy FROM action.hold_request WHERE id = $1),
2332 (SELECT usr FROM action.hold_request WHERE id = $1),
2333 (SELECT requestor FROM action.hold_request WHERE id = $1)
2337 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2338 SELECT action.find_circ_matrix_matchpoint(
2339 (SELECT circ_lib FROM action.circulation WHERE id = $1),
2340 (SELECT target_copy FROM action.circulation WHERE id = $1),
2341 (SELECT usr FROM action.circulation WHERE id = $1),
2343 NULLIF(phone_renewal,false),
2344 NULLIF(desk_renewal,false),
2345 NULLIF(opac_renewal,false),
2347 ) FROM action.circulation WHERE id = $1