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.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'' );' );
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 );' );
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 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$
629 attempt_value ALIAS FOR $1;
630 datatype ALIAS FOR $2;
631 fail_value ALIAS FOR $3;
635 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;'
642 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::' || datatype || ' AS a;'
647 $$ LANGUAGE PLPGSQL STRICT STABLE;
649 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
651 attempt_value ALIAS FOR $1;
652 fail_value ALIAS FOR $2;
656 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::date AS a;'
663 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
668 $$ LANGUAGE PLPGSQL STRICT STABLE;
670 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
672 attempt_value ALIAS FOR $1;
673 fail_value ALIAS FOR $2;
677 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
684 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
689 $$ LANGUAGE PLPGSQL STRICT STABLE;
691 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
693 attempt_value ALIAS FOR $1;
694 fail_value ALIAS FOR $2;
698 EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;'
705 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
710 $$ LANGUAGE PLPGSQL STRICT STABLE;
712 -- add_codabar_checkdigit
713 -- $barcode source barcode
715 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
716 -- character with a checkdigit computed according to the usual algorithm for library barcodes
717 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
718 -- input string does not meet those requirements, it is returned unchanged.
720 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
723 return $barcode if $barcode !~ /^\d{13,14}$/;
724 $barcode = substr($barcode, 0, 13); # ignore 14th digit
725 my @digits = split //, $barcode;
727 $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
728 $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
729 my $remainder = $total % 10;
730 my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
731 return $barcode . $checkdigit;
732 $$ LANGUAGE PLPERLU STRICT STABLE;
734 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
740 n_digits INTEGER := 0;
743 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
744 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
745 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
746 IF n_digits = 7 AND areacode <> '' THEN
747 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
748 output := (areacode || '-' || temp);
755 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
757 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
758 my ($marcxml, $pos, $value) = @_;
765 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
766 my $leader = $marc->leader();
767 substr($leader, $pos, 1) = $value;
768 $marc->leader($leader);
769 $xml = $marc->as_xml_record;
770 $xml =~ s/^<\?.+?\?>$//mo;
772 $xml =~ s/>\s+</></sgo;
775 $$ LANGUAGE PLPERLU STABLE;
777 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
778 my ($marcxml, $pos, $value) = @_;
785 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
786 my $f008 = $marc->field('008');
789 my $field = $f008->data();
790 substr($field, $pos, 1) = $value;
791 $f008->update($field);
792 $xml = $marc->as_xml_record;
793 $xml =~ s/^<\?.+?\?>$//mo;
795 $xml =~ s/>\s+</></sgo;
799 $$ LANGUAGE PLPERLU STABLE;
802 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
804 profile ALIAS FOR $1;
806 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
808 $$ LANGUAGE PLPGSQL STRICT STABLE;
811 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
813 RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
815 $$ LANGUAGE PLPGSQL STRICT STABLE;
818 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
820 my ($marcxml, $tags) = @_;
828 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
829 my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
833 foreach my $field ( $marc->fields() ) {
834 push @incumbents, $field->as_formatted();
837 foreach $field ( $to_insert->fields() ) {
838 if (!grep {$_ eq $field->as_formatted()} @incumbents) {
839 $marc->insert_fields_ordered( ($field) );
843 $xml = $marc->as_xml_record;
844 $xml =~ s/^<\?.+?\?>$//mo;
846 $xml =~ s/>\s+</></sgo;
851 $$ LANGUAGE PLPERLU STABLE;
853 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
857 -- First make sure the circ matrix is loaded and the circulations
858 -- have been staged to the extent possible (but at the very least
859 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
860 -- circ modifiers must also be in place.
862 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
870 this_duration_rule INT;
872 this_max_fine_rule INT;
873 rcd config.rule_circ_duration%ROWTYPE;
874 rrf config.rule_recurring_fine%ROWTYPE;
875 rmf config.rule_max_fine%ROWTYPE;
882 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
884 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
886 -- Fetch the correct rules for this circulation
893 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
896 FROM ' || tablename || ' WHERE id = ' || circ || ';')
897 INTO circ_lib, target_copy, usr, is_renewal ;
899 INTO this_duration_rule,
905 FROM action.item_user_circ_test(
911 SELECT INTO rcd * FROM config.rule_circ_duration
912 WHERE id = this_duration_rule;
913 SELECT INTO rrf * FROM config.rule_recurring_fine
914 WHERE id = this_fine_rule;
915 SELECT INTO rmf * FROM config.rule_max_fine
916 WHERE id = this_max_fine_rule;
918 -- Apply the rules to this circulation
919 EXECUTE ('UPDATE ' || tablename || ' c
921 duration_rule = rcd.name,
922 recurring_fine_rule = rrf.name,
923 max_fine_rule = rmf.name,
924 duration = rcd.normal,
925 recurring_fine = rrf.normal,
928 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
931 renewal_remaining = rcd.max_renewals
933 config.rule_circ_duration rcd,
934 config.rule_recurring_fine rrf,
935 config.rule_max_fine rmf,
938 rcd.id = ' || this_duration_rule || ' AND
939 rrf.id = ' || this_fine_rule || ' AND
940 rmf.id = ' || this_max_fine_rule || ' AND
941 ac.id = c.target_copy AND
942 c.id = ' || circ || ';');
944 -- Keep track of where we are in the process
946 IF (n % 100 = 0) THEN
947 RAISE INFO '%', n || ' of ' || n_circs
948 || ' (' || (100*n/n_circs) || '%) circs updated.';
958 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
962 -- First make sure the circ matrix is loaded and the circulations
963 -- have been staged to the extent possible (but at the very least
964 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
965 -- circ modifiers must also be in place.
967 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
975 this_duration_rule INT;
977 this_max_fine_rule INT;
978 rcd config.rule_circ_duration%ROWTYPE;
979 rrf config.rule_recurring_fine%ROWTYPE;
980 rmf config.rule_max_fine%ROWTYPE;
987 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
989 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
991 -- Fetch the correct rules for this circulation
998 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1001 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1002 INTO circ_lib, target_copy, usr, is_renewal ;
1004 INTO this_duration_rule,
1010 FROM action.find_circ_matrix_matchpoint(
1016 SELECT INTO rcd * FROM config.rule_circ_duration
1017 WHERE id = this_duration_rule;
1018 SELECT INTO rrf * FROM config.rule_recurring_fine
1019 WHERE id = this_fine_rule;
1020 SELECT INTO rmf * FROM config.rule_max_fine
1021 WHERE id = this_max_fine_rule;
1023 -- Apply the rules to this circulation
1024 EXECUTE ('UPDATE ' || tablename || ' c
1026 duration_rule = rcd.name,
1027 recuring_fine_rule = rrf.name,
1028 max_fine_rule = rmf.name,
1029 duration = rcd.normal,
1030 recuring_fine = rrf.normal,
1033 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1036 renewal_remaining = rcd.max_renewals
1038 config.rule_circ_duration rcd,
1039 config.rule_recuring_fine rrf,
1040 config.rule_max_fine rmf,
1043 rcd.id = ' || this_duration_rule || ' AND
1044 rrf.id = ' || this_fine_rule || ' AND
1045 rmf.id = ' || this_max_fine_rule || ' AND
1046 ac.id = c.target_copy AND
1047 c.id = ' || circ || ';');
1049 -- Keep track of where we are in the process
1051 IF (n % 100 = 0) THEN
1052 RAISE INFO '%', n || ' of ' || n_circs
1053 || ' (' || (100*n/n_circs) || '%) circs updated.';
1061 $$ LANGUAGE plpgsql;
1063 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1067 -- First make sure the circ matrix is loaded and the circulations
1068 -- have been staged to the extent possible (but at the very least
1069 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
1070 -- circ modifiers must also be in place.
1072 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1080 this_duration_rule INT;
1082 this_max_fine_rule INT;
1083 rcd config.rule_circ_duration%ROWTYPE;
1084 rrf config.rule_recurring_fine%ROWTYPE;
1085 rmf config.rule_max_fine%ROWTYPE;
1092 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1094 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1096 -- Fetch the correct rules for this circulation
1103 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1106 FROM ' || tablename || ' WHERE id = ' || circ || ';')
1107 INTO circ_lib, target_copy, usr, is_renewal ;
1109 INTO this_duration_rule,
1112 (matchpoint).duration_rule,
1113 (matchpoint).recurring_fine_rule,
1114 (matchpoint).max_fine_rule
1115 FROM action.find_circ_matrix_matchpoint(
1121 SELECT INTO rcd * FROM config.rule_circ_duration
1122 WHERE id = this_duration_rule;
1123 SELECT INTO rrf * FROM config.rule_recurring_fine
1124 WHERE id = this_fine_rule;
1125 SELECT INTO rmf * FROM config.rule_max_fine
1126 WHERE id = this_max_fine_rule;
1128 -- Apply the rules to this circulation
1129 EXECUTE ('UPDATE ' || tablename || ' c
1131 duration_rule = rcd.name,
1132 recurring_fine_rule = rrf.name,
1133 max_fine_rule = rmf.name,
1134 duration = rcd.normal,
1135 recurring_fine = rrf.normal,
1138 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1141 renewal_remaining = rcd.max_renewals,
1142 grace_period = rrf.grace_period
1144 config.rule_circ_duration rcd,
1145 config.rule_recurring_fine rrf,
1146 config.rule_max_fine rmf,
1149 rcd.id = ' || this_duration_rule || ' AND
1150 rrf.id = ' || this_fine_rule || ' AND
1151 rmf.id = ' || this_max_fine_rule || ' AND
1152 ac.id = c.target_copy AND
1153 c.id = ' || circ || ';');
1155 -- Keep track of where we are in the process
1157 IF (n % 100 = 0) THEN
1158 RAISE INFO '%', n || ' of ' || n_circs
1159 || ' (' || (100*n/n_circs) || '%) circs updated.';
1167 $$ LANGUAGE plpgsql;
1172 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1174 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1175 -- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1177 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1178 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1181 c TEXT := schemaname || '.asset_copy_legacy';
1182 sc TEXT := schemaname || '.asset_stat_cat';
1183 sce TEXT := schemaname || '.asset_stat_cat_entry';
1184 scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1190 FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1192 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1194 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1195 SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1196 (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1203 $$ LANGUAGE plpgsql;
1205 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1207 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1208 -- This will assign standing penalties as needed.
1216 FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1218 FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1220 EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1230 $$ LANGUAGE plpgsql;
1233 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1236 INSERT INTO metabib.metarecord (fingerprint, master_record)
1237 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1238 FROM biblio.record_entry b
1240 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)
1241 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1242 ORDER BY b.fingerprint, b.quality DESC;
1243 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1245 FROM biblio.record_entry r
1246 JOIN metabib.metarecord m USING (fingerprint)
1247 WHERE NOT r.deleted;
1250 $$ LANGUAGE plpgsql;
1253 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1256 INSERT INTO metabib.metarecord (fingerprint, master_record)
1257 SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1258 FROM biblio.record_entry b
1260 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)
1261 AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1262 ORDER BY b.fingerprint, b.quality DESC;
1263 INSERT INTO metabib.metarecord_source_map (metarecord, source)
1265 FROM biblio.record_entry r
1266 JOIN metabib.metarecord m USING (fingerprint)
1268 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);
1271 $$ LANGUAGE plpgsql;
1274 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1276 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1277 -- Then SELECT migration_tools.create_cards('m_foo');
1280 u TEXT := schemaname || '.actor_usr_legacy';
1281 c TEXT := schemaname || '.actor_card';
1285 EXECUTE ('DELETE FROM ' || c || ';');
1286 EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1287 EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1293 $$ LANGUAGE plpgsql;
1296 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1298 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1300 my ($marcxml, $shortname) = @_;
1303 use MARC::File::XML;
1308 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1310 foreach my $field ( $marc->field('856') ) {
1311 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1312 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1313 $field->add_subfields( '9' => $shortname );
1314 $field->update( ind2 => '0');
1318 $xml = $marc->as_xml_record;
1319 $xml =~ s/^<\?.+?\?>$//mo;
1321 $xml =~ s/>\s+</></sgo;
1326 $$ LANGUAGE PLPERLU STABLE;
1328 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1340 -- Bail out if asked to change the label to ##URI##
1341 IF new_label = '##URI##' THEN
1345 -- Gather information
1346 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1347 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1348 SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1350 -- Bail out if the label already is ##URI##
1351 IF old_label = '##URI##' THEN
1355 -- Bail out if the call number label is already correct
1356 IF new_volume = old_volume THEN
1360 -- Check whether we already have a destination volume available
1361 SELECT id INTO new_volume FROM asset.call_number
1364 owning_lib = owner AND
1365 label = new_label AND
1368 -- Create destination volume if needed
1370 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class)
1371 VALUES (1, 1, bib, owner, new_label, cn_class);
1372 SELECT id INTO new_volume FROM asset.call_number
1375 owning_lib = owner AND
1376 label = new_label AND
1380 -- Move copy to destination
1381 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1383 -- Delete source volume if it is now empty
1384 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1386 DELETE FROM asset.call_number WHERE id = old_volume;
1391 $$ LANGUAGE plpgsql;
1393 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1398 open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1402 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1403 $zipdata{$zip} = [$city, $state, $county];
1406 if (defined $zipdata{$input}) {
1407 my ($city, $state, $county) = @{$zipdata{$input}};
1408 return [$city, $state, $county];
1409 } elsif (defined $zipdata{substr $input, 0, 5}) {
1410 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1411 return [$city, $state, $county];
1413 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1416 $$ LANGUAGE PLPERLU STABLE;
1418 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1425 errors_found BOOLEAN;
1427 parent_shortname TEXT;
1433 type_parent_depth INT;
1438 errors_found := FALSE;
1440 -- Checking actor.org_unit_type
1442 FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1444 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1445 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1447 IF type_parent IS NOT NULL THEN
1449 SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1451 IF type_depth - type_parent_depth <> 1 THEN
1452 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1453 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1454 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1455 ou_type_name, type_depth, parent_type, type_parent_depth;
1456 errors_found := TRUE;
1464 -- Checking actor.org_unit
1466 FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1468 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1469 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;
1470 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;
1471 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1472 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1473 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;
1474 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;
1476 IF ou_parent IS NOT NULL THEN
1478 IF (org_unit_depth - parent_depth <> 1) OR (
1479 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1481 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
1482 ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1483 errors_found := TRUE;
1490 IF NOT errors_found THEN
1491 RAISE INFO 'No errors found.';
1498 $$ LANGUAGE plpgsql;
1501 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1505 DELETE FROM asset.opac_visible_copies;
1507 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1509 cp.id, cp.circ_lib, cn.record
1512 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1513 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1514 JOIN asset.copy_location cl ON (cp.location = cl.id)
1515 JOIN config.copy_status cs ON (cp.status = cs.id)
1516 JOIN biblio.record_entry b ON (cn.record = b.id)
1525 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
1529 $$ LANGUAGE plpgsql;
1532 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
1538 old_owning_lib INTEGER;
1544 -- Gather information
1545 SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1546 SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1547 SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
1549 -- Bail out if the new_owning_lib is not the ID of an org_unit
1550 IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
1552 '% is not a valid actor.org_unit ID; no change made.',
1557 -- Bail out discreetly if the owning_lib is already correct
1558 IF new_owning_lib = old_owning_lib THEN
1562 -- Check whether we already have a destination volume available
1563 SELECT id INTO new_volume FROM asset.call_number
1566 owning_lib = new_owning_lib AND
1567 label = old_label AND
1570 -- Create destination volume if needed
1572 INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
1573 VALUES (1, 1, bib, new_owning_lib, old_label);
1574 SELECT id INTO new_volume FROM asset.call_number
1577 owning_lib = new_owning_lib AND
1578 label = old_label AND
1582 -- Move copy to destination
1583 UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1585 -- Delete source volume if it is now empty
1586 SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1588 DELETE FROM asset.call_number WHERE id = old_volume;
1593 $$ LANGUAGE plpgsql;
1596 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
1598 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
1601 new_owning_lib INTEGER;
1605 -- Parse the new_owner as an org unit ID or shortname
1606 IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
1607 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
1608 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1609 ELSIF new_owner ~ E'^[0-9]+$' THEN
1610 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
1613 E'You don\'t need to put the actor.org_unit ID in quotes; '
1614 || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
1615 new_owning_lib := new_owner::INTEGER;
1616 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
1620 '% is not a valid actor.org_unit shortname or ID; no change made.',
1627 $$ LANGUAGE plpgsql;
1629 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
1632 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1635 MARC::Charset->assume_unicode(1);
1639 eval { my $r = MARC::Record->new_from_xml( $xml ); };
1646 $func$ LANGUAGE PLPERLU;
1647 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
1649 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
1651 EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
1652 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1653 $$) TO '$$ || dir || $$/actor_hours_of_operation'$$;
1654 EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
1655 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1656 $$) TO '$$ || dir || $$/actor_org_unit_closed'$$;
1657 EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
1658 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1659 $$) TO '$$ || dir || $$/actor_org_unit_setting'$$;
1660 EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
1661 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1662 $$) TO '$$ || dir || $$/asset_copy_location'$$;
1663 EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
1664 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1665 $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1666 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
1667 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1668 $$) TO '$$ || dir || $$/asset_call_number_prefix'$$;
1669 EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
1670 ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
1671 $$) TO '$$ || dir || $$/asset_call_number_suffix'$$;
1672 EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$;
1673 EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$;
1674 EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$;
1675 EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$;
1676 EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$;
1678 $FUNC$ LANGUAGE PLPGSQL;
1680 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
1682 EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$;
1683 EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$;
1684 EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$;
1685 EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$;
1686 EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$;
1687 EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$;
1688 EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$;
1690 -- import any new circ rules
1691 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
1692 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
1693 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
1694 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
1696 -- and permission groups
1697 PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
1700 $FUNC$ LANGUAGE PLPGSQL;
1703 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$
1712 EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
1713 EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
1714 EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$;
1715 loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
1716 existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
1717 SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
1718 FOR name IN EXECUTE loopq LOOP
1719 EXECUTE existsq INTO ct USING name;
1721 RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
1722 copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
1723 ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
1724 EXECUTE copyst USING name;
1728 $FUNC$ LANGUAGE PLPGSQL;
1730 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
1736 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1739 MARC::Charset->assume_unicode(1);
1741 my $target_xml = shift;
1742 my $source_xml = shift;
1748 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
1752 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
1757 my $source_id = $source->subfield('901', 'c');
1758 $source_id = $source->subfield('903', 'a') unless $source_id;
1759 my $target_id = $target->subfield('901', 'c');
1760 $target_id = $target->subfield('903', 'a') unless $target_id;
1762 my %existing_fields;
1763 foreach my $tag (@$tags) {
1764 my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
1765 my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
1766 $target->insert_fields_ordered(map { $_->clone() } @to_add);
1768 elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
1772 my $xml = $target->as_xml_record;
1773 $xml =~ s/^<\?.+?\?>$//mo;
1775 $xml =~ s/>\s+</></sgo;
1779 $func$ LANGUAGE PLPERLU;
1780 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.';
1782 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
1788 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1791 my $in_tags = shift;
1792 my $in_values = shift;
1794 # hack-and-slash parsing of array-passed-as-string;
1795 # this can go away once everybody is running Postgres 9.1+
1796 my $csv = Text::CSV->new({binary => 1});
1799 my $status = $csv->parse($in_tags);
1800 my $tags = [ $csv->fields() ];
1801 $in_values =~ s/^{//;
1802 $in_values =~ s/}$//;
1803 $status = $csv->parse($in_values);
1804 my $values = [ $csv->fields() ];
1806 my $marc = MARC::Record->new();
1808 $marc->leader('00000nam a22000007 4500');
1809 $marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
1811 foreach my $i (0..$#$tags) {
1813 if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
1816 $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1817 } elsif ($tags->[$i] =~ /^(\d{3})$/) {
1819 $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
1823 my $xml = $marc->as_xml_record;
1824 $xml =~ s/^<\?.+?\?>$//mo;
1826 $xml =~ s/>\s+</></sgo;
1830 $func$ LANGUAGE PLPERLU;
1831 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
1832 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
1833 The second argument is an array of text containing the values to plug into each field.
1834 If the value for a given field is NULL or the empty string, it is not inserted.
1837 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
1839 my ($marcxml, $tag, $pos, $value) = @_;
1842 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1846 MARC::Charset->assume_unicode(1);
1848 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
1849 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
1850 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
1851 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
1855 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1857 foreach my $field ($marc->field($tag)) {
1858 $field->update("ind$pos" => $value);
1860 $xml = $marc->as_xml_record;
1861 $xml =~ s/^<\?.+?\?>$//mo;
1863 $xml =~ s/>\s+</></sgo;
1867 $func$ LANGUAGE PLPERLU;
1869 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
1870 The first argument is a MARCXML string.
1871 The second argument is a MARC tag.
1872 The third argument is the indicator position, either 1 or 2.
1873 The fourth argument is the character to set the indicator value to.
1874 All occurences of the specified field will be changed.
1875 The function returns the revised MARCXML string.$$;