migration_tools.split_rows_on_column_with_delimiter
[migration-tools.git] / sql / base / base.sql
1 -- Copyright 2009-2012, Equinox Software, Inc.
2 --
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.
7 --
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.
12 --
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.
16
17 --------------------------------------------------------------------------
18 -- An example of how to use:
19 -- 
20 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo; 
21 -- \i base.sql
22 -- SELECT migration_tools.init('foo');
23 -- SELECT migration_tools.build('foo');
24 -- SELECT * FROM foo.fields_requiring_mapping;
25 -- \d foo.actor_usr
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')
29
30 CREATE SCHEMA migration_tools;
31
32 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
33     DECLARE
34         migration_schema ALIAS FOR $1;
35         output  RECORD;
36     BEGIN
37         FOR output IN
38             EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
39         LOOP
40             RETURN output.tables;
41         END LOOP;
42     END;
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
44
45 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
46     DECLARE
47         migration_schema ALIAS FOR $1;
48         output TEXT;
49     BEGIN
50         FOR output IN
51             EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
52         LOOP
53             RETURN output;
54         END LOOP;
55     END;
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
57
58
59 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
60     DECLARE
61         migration_schema ALIAS FOR $1;
62         sql ALIAS FOR $2;
63         nrows ALIAS FOR $3;
64     BEGIN
65         EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
66     END;
67 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
68
69 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
70     DECLARE
71         migration_schema ALIAS FOR $1;
72         sql ALIAS FOR $2;
73         nrows INTEGER;
74     BEGIN
75         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
76         --RAISE INFO '%', sql;
77         EXECUTE sql;
78         GET DIAGNOSTICS nrows = ROW_COUNT;
79         PERFORM migration_tools.log(migration_schema,sql,nrows);
80     EXCEPTION
81         WHEN OTHERS THEN 
82             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
83     END;
84 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
85
86 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
87     DECLARE
88         migration_schema ALIAS FOR $1;
89         sql ALIAS FOR $2;
90         nrows INTEGER;
91     BEGIN
92         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
93         RAISE INFO 'debug_exec sql = %', sql;
94         EXECUTE sql;
95         GET DIAGNOSTICS nrows = ROW_COUNT;
96         PERFORM migration_tools.log(migration_schema,sql,nrows);
97     EXCEPTION
98         WHEN OTHERS THEN 
99             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
100     END;
101 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
102
103 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
104     DECLARE
105         migration_schema ALIAS FOR $1;
106         sql TEXT;
107     BEGIN
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 ( '''' );';
111         BEGIN
112             SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
113             EXECUTE sql;
114         EXCEPTION
115             WHEN OTHERS THEN 
116                 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
117         END;
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,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry,biblio.record_entry'' );' );
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 ( 
126             id SERIAL,
127             perm_grp_id INTEGER,
128             transcribed_perm_group TEXT,
129             legacy_field1 TEXT,
130             legacy_value1 TEXT,
131             legacy_field2 TEXT,
132             legacy_value2 TEXT,
133             legacy_field3 TEXT,
134             legacy_value3 TEXT
135         );' );
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 ( 
139             id SERIAL,
140             evergreen_field TEXT,
141             evergreen_value TEXT,
142             evergreen_datatype TEXT,
143             legacy_field1 TEXT,
144             legacy_value1 TEXT,
145             legacy_field2 TEXT,
146             legacy_value2 TEXT,
147             legacy_field3 TEXT,
148             legacy_value3 TEXT
149         );' );
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 ( 
156             id SERIAL,
157             location INTEGER,
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,
163             legacy_field1 TEXT,
164             legacy_value1 TEXT,
165             legacy_field2 TEXT,
166             legacy_value2 TEXT,
167             legacy_field3 TEXT,
168             legacy_value3 TEXT
169         );' );
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 ( 
177             id SERIAL,
178             circulate BOOLEAN,
179             loan_period TEXT,
180             max_renewals TEXT,
181             max_out TEXT,
182             fine_amount TEXT,
183             fine_interval TEXT,
184             max_fine TEXT,
185             item_field1 TEXT,
186             item_value1 TEXT,
187             item_field2 TEXT,
188             item_value2 TEXT,
189             patron_field1 TEXT,
190             patron_value1 TEXT,
191             patron_field2 TEXT,
192             patron_value2 TEXT
193         );' );
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'' );' );
199
200         BEGIN
201             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
202         EXCEPTION
203             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
204         END;
205     END;
206 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
207
208 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
209     DECLARE
210         migration_schema ALIAS FOR $1;
211         production_tables TEXT[];
212     BEGIN
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 );' );
223     END;
224 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
225
226 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
227     DECLARE
228         migration_schema ALIAS FOR $1;
229         production_tables ALIAS FOR $2;
230     BEGIN
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]);
234         END LOOP;
235     END;
236 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
237
238 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
239     DECLARE
240         migration_schema ALIAS FOR $1;
241         production_table ALIAS FOR $2;
242         base_staging_table TEXT;
243         columns RECORD;
244     BEGIN
245         base_staging_table = REPLACE( production_table, '.', '_' );
246         --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
247         PERFORM migration_tools.exec( $1, 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
248         PERFORM migration_tools.exec( $1, '
249             INSERT INTO ' || migration_schema || '.fields_requiring_mapping
250                 SELECT table_schema, table_name, column_name, data_type
251                 FROM information_schema.columns 
252                 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
253         ' );
254         FOR columns IN 
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
258         LOOP
259             PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
260         END LOOP;
261     END;
262 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
263
264 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
265     DECLARE
266         migration_schema ALIAS FOR $1;
267         production_tables TEXT[];
268     BEGIN
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]);
273         END LOOP;
274     END;
275 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
276
277 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
278     DECLARE
279         migration_schema ALIAS FOR $1;
280         production_table ALIAS FOR $2;
281         base_staging_table TEXT;
282         columns RECORD;
283     BEGIN
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 || ';' );
287     END;
288 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
289
290 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
291     DECLARE
292         full_name TEXT := $1;
293         before_comma TEXT;
294         family_name TEXT := '';
295         first_given_name TEXT := '';
296         second_given_name TEXT := '';
297         suffix TEXT := '';
298         prefix TEXT := '';
299     BEGIN
300         before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
301         suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
302
303         IF suffix = before_comma THEN
304             suffix := '';
305         END IF;
306
307         family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
308         first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
309         second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
310
311         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
312     END;
313 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
314
315 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
316     DECLARE
317         full_name TEXT := $1;
318         temp TEXT;
319         family_name TEXT := '';
320         first_given_name TEXT := '';
321         second_given_name TEXT := '';
322         suffix TEXT := '';
323         prefix TEXT := '';
324     BEGIN
325         temp := full_name;
326         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
327         IF temp ilike '%MR.%' THEN
328             prefix := 'Mr.';
329             temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
330         END IF;
331         IF temp ilike '%MRS.%' THEN
332             prefix := 'Mrs.';
333             temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
334         END IF;
335         IF temp ilike '%MS.%' THEN
336             prefix := 'Ms.';
337             temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
338         END IF;
339         IF temp ilike '%DR.%' THEN
340             prefix := 'Dr.';
341             temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
342         END IF;
343         IF temp ilike '%JR%' THEN
344             suffix := 'Jr.';
345             temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
346         END IF;
347         IF temp ilike '%JR,%' THEN
348             suffix := 'Jr.';
349             temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
350         END IF;
351         IF temp ilike '%SR%' THEN
352             suffix := 'Sr.';
353             temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
354         END IF;
355         IF temp ilike '%SR,%' THEN
356             suffix := 'Sr.';
357             temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
358         END IF;
359         IF temp ~ E'\\sII$' THEN
360             suffix := 'II';
361             temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
362         END IF;
363         IF temp ~ E'\\sIII$' THEN
364             suffix := 'III';
365             temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
366         END IF;
367         IF temp ~ E'\\sIV$' THEN
368             suffix := 'IV';
369             temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
370         END IF;
371
372         family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
373         first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
374         second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE ''  END );
375
376         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
377     END;
378 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
379
380 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
381     DECLARE
382         full_name TEXT := $1;
383         temp TEXT;
384         family_name TEXT := '';
385         first_given_name TEXT := '';
386         second_given_name TEXT := '';
387         suffix TEXT := '';
388         prefix TEXT := '';
389     BEGIN
390         temp := full_name;
391         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
392         IF temp ilike '%MR.%' THEN
393             prefix := 'Mr.';
394             temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
395         END IF;
396         IF temp ilike '%MRS.%' THEN
397             prefix := 'Mrs.';
398             temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
399         END IF;
400         IF temp ilike '%MS.%' THEN
401             prefix := 'Ms.';
402             temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
403         END IF;
404         IF temp ilike '%DR.%' THEN
405             prefix := 'Dr.';
406             temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
407         END IF;
408         IF temp ilike '%JR.%' THEN
409             suffix := 'Jr.';
410             temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
411         END IF;
412         IF temp ilike '%JR,%' THEN
413             suffix := 'Jr.';
414             temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
415         END IF;
416         IF temp ilike '%SR.%' THEN
417             suffix := 'Sr.';
418             temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
419         END IF;
420         IF temp ilike '%SR,%' THEN
421             suffix := 'Sr.';
422             temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
423         END IF;
424         IF temp like '%III%' THEN
425             suffix := 'III';
426             temp := REGEXP_REPLACE( temp, E'III', '' );
427         END IF;
428         IF temp like '%II%' THEN
429             suffix := 'II';
430             temp := REGEXP_REPLACE( temp, E'II', '' );
431         END IF;
432         IF temp like '%IV%' THEN
433             suffix := 'IV';
434             temp := REGEXP_REPLACE( temp, E'IV', '' );
435         END IF;
436
437         temp := REGEXP_REPLACE( temp, '\(\)', '');
438         family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
439         family_name := REGEXP_REPLACE( family_name, ',', '' );
440         first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
441         first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
442         second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
443         second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
444
445         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
446     END;
447 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
448
449 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
450     DECLARE
451         full_name TEXT := $1;
452         temp TEXT;
453         family_name TEXT := '';
454         first_given_name TEXT := '';
455         second_given_name TEXT := '';
456         suffix TEXT := '';
457         prefix TEXT := '';
458     BEGIN
459         temp := BTRIM(full_name);
460         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
461         --IF temp ~ '^\S{2,}\.' THEN
462         --    prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
463         --    temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
464         --END IF;
465         --IF temp ~ '\S{2,}\.$' THEN
466         --    suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
467         --    temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
468         --END IF;
469         IF temp ilike '%MR.%' THEN
470             prefix := 'Mr.';
471             temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
472         END IF;
473         IF temp ilike '%MRS.%' THEN
474             prefix := 'Mrs.';
475             temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
476         END IF;
477         IF temp ilike '%MS.%' THEN
478             prefix := 'Ms.';
479             temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
480         END IF;
481         IF temp ilike '%DR.%' THEN
482             prefix := 'Dr.';
483             temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
484         END IF;
485         IF temp ilike '%JR.%' THEN
486             suffix := 'Jr.';
487             temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
488         END IF;
489         IF temp ilike '%JR,%' THEN
490             suffix := 'Jr.';
491             temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
492         END IF;
493         IF temp ilike '%SR.%' THEN
494             suffix := 'Sr.';
495             temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
496         END IF;
497         IF temp ilike '%SR,%' THEN
498             suffix := 'Sr.';
499             temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
500         END IF;
501         IF temp like '%III%' THEN
502             suffix := 'III';
503             temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
504         END IF;
505         IF temp like '%II%' THEN
506             suffix := 'II';
507             temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
508         END IF;
509
510         IF temp ~ ',' THEN
511             family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
512             temp := BTRIM(REPLACE( temp, family_name, '' ));
513             family_name := REPLACE( family_name, ',', '' );
514             IF temp ~ ' ' THEN
515                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
516                 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
517             ELSE
518                 first_given_name := temp;
519                 second_given_name := '';
520             END IF;
521         ELSE
522             IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
523                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
524                 second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
525                 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
526             ELSE
527                 first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
528                 second_given_name := temp;
529                 family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
530             END IF;
531         END IF;
532
533         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
534     END;
535 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
536
537 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
538     DECLARE
539         city_state_zip TEXT := $1;
540         city TEXT := '';
541         state TEXT := '';
542         zip TEXT := '';
543     BEGIN
544         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;
545         city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
546         IF city_state_zip ~ ',' THEN
547             state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
548             city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
549         ELSE
550             IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
551                 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
552                 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
553             ELSE
554                 IF city_state_zip ~ E'^\\S+$'  THEN
555                     city := city_state_zip;
556                     state := 'N/A';
557                 ELSE
558                     state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
559                     city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
560                 END IF;
561             END IF;
562         END IF;
563         RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
564     END;
565 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
566
567 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
568 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
569     DECLARE
570         fullstring TEXT := $1;
571         address1 TEXT := '';
572         address2 TEXT := '';
573         scratch1 TEXT := '';
574         scratch2 TEXT := '';
575         city TEXT := '';
576         state TEXT := '';
577         zip TEXT := '';
578     BEGIN
579         zip := CASE
580             WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
581             THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
582             ELSE ''
583         END;
584         fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
585
586         IF fullstring ~ ',' THEN
587             state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
588             scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
589         ELSE
590             IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
591                 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
592                 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
593             ELSE
594                 IF fullstring ~ E'^\\S+$'  THEN
595                     scratch1 := fullstring;
596                     state := 'N/A';
597                 ELSE
598                     state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
599                     scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
600                 END IF;
601             END IF;
602         END IF;
603
604         IF scratch1 ~ '[\$]' THEN
605             scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
606             city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
607         ELSE
608             IF scratch1 ~ '\s' THEN
609                 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
610                 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
611             ELSE
612                 scratch2 := 'N/A';
613                 city := scratch1;
614             END IF;
615         END IF;
616
617         IF scratch2 ~ '^\d' THEN
618             address1 := scratch2;
619             address2 := '';
620         ELSE
621             address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
622             address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
623         END IF;
624
625         RETURN ARRAY[
626              TRIM(BOTH ' ' FROM address1)
627             ,TRIM(BOTH ' ' FROM address2)
628             ,TRIM(BOTH ' ' FROM city)
629             ,TRIM(BOTH ' ' FROM state)
630             ,TRIM(BOTH ' ' FROM zip)
631         ];
632     END;
633 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
634
635 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
636     my ($address) = @_;
637
638     use Geo::StreetAddress::US;
639
640     my $a = Geo::StreetAddress::US->parse_location($address);
641
642     return [
643          "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
644         ,"$a->{sec_unit_type} $a->{sec_unit_num}"
645         ,$a->{city}
646         ,$a->{state}
647         ,$a->{zip}
648     ];
649 $$ LANGUAGE PLPERLU STABLE;
650
651 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
652     DECLARE
653         n TEXT := o;
654     BEGIN
655         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
656             IF o::BIGINT < t THEN
657                 n = o::BIGINT + t;
658             END IF;
659         END IF;
660
661         RETURN n;
662     END;
663 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
664
665 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
666     DECLARE
667         migration_schema ALIAS FOR $1;
668         output TEXT;
669     BEGIN
670         FOR output IN
671             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
672         LOOP
673             RETURN output;
674         END LOOP;
675     END;
676 $$ LANGUAGE PLPGSQL STRICT STABLE;
677
678 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
679     DECLARE
680         migration_schema ALIAS FOR $1;
681         output TEXT;
682     BEGIN
683         FOR output IN
684             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
685         LOOP
686             RETURN output;
687         END LOOP;
688     END;
689 $$ LANGUAGE PLPGSQL STRICT STABLE;
690
691 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
692     DECLARE
693         migration_schema ALIAS FOR $1;
694         output TEXT;
695     BEGIN
696         FOR output IN
697             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
698         LOOP
699             RETURN output;
700         END LOOP;
701     END;
702 $$ LANGUAGE PLPGSQL STRICT STABLE;
703
704 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
705     DECLARE
706         migration_schema ALIAS FOR $1;
707         output TEXT;
708     BEGIN
709         FOR output IN
710             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
711         LOOP
712             RETURN output;
713         END LOOP;
714     END;
715 $$ LANGUAGE PLPGSQL STRICT STABLE;
716
717 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
718     DECLARE
719         migration_schema ALIAS FOR $1;
720         profile_map TEXT;
721         patron_table ALIAS FOR $2;
722         default_patron_profile ALIAS FOR $3;
723         sql TEXT;
724         sql_update TEXT;
725         sql_where1 TEXT := '';
726         sql_where2 TEXT := '';
727         sql_where3 TEXT := '';
728         output RECORD;
729     BEGIN
730         SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
731         FOR output IN 
732             EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
733         LOOP
734             sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
735             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);
736             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);
737             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);
738             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,'') || ';';
739             --RAISE INFO 'sql = %', sql;
740             PERFORM migration_tools.exec( $1, sql );
741         END LOOP;
742         PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;'  );
743         BEGIN
744             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
745         EXCEPTION
746             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
747         END;
748     END;
749 $$ LANGUAGE PLPGSQL STRICT STABLE;
750
751 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
752     DECLARE
753         migration_schema ALIAS FOR $1;
754         field_map TEXT;
755         item_table ALIAS FOR $2;
756         sql TEXT;
757         sql_update TEXT;
758         sql_where1 TEXT := '';
759         sql_where2 TEXT := '';
760         sql_where3 TEXT := '';
761         output RECORD;
762     BEGIN
763         SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
764         FOR output IN 
765             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
766         LOOP
767             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 ';
768             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);
769             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);
770             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);
771             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,'') || ';';
772             --RAISE INFO 'sql = %', sql;
773             PERFORM migration_tools.exec( $1, sql );
774         END LOOP;
775         BEGIN
776             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
777         EXCEPTION
778             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
779         END;
780     END;
781 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
782
783 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
784     DECLARE
785         migration_schema ALIAS FOR $1;
786         base_copy_location_map TEXT;
787         item_table ALIAS FOR $2;
788         sql TEXT;
789         sql_update TEXT;
790         sql_where1 TEXT := '';
791         sql_where2 TEXT := '';
792         sql_where3 TEXT := '';
793         output RECORD;
794     BEGIN
795         SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
796         FOR output IN 
797             EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
798         LOOP
799             sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
800             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);
801             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);
802             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);
803             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,'') || ';';
804             --RAISE INFO 'sql = %', sql;
805             PERFORM migration_tools.exec( $1, sql );
806         END LOOP;
807         BEGIN
808             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
809         EXCEPTION
810             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
811         END;
812     END;
813 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
814
815 -- 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
816 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
817     DECLARE
818         migration_schema ALIAS FOR $1;
819         field_map TEXT;
820         circ_table ALIAS FOR $2;
821         item_table ALIAS FOR $3;
822         patron_table ALIAS FOR $4;
823         sql TEXT;
824         sql_update TEXT;
825         sql_where1 TEXT := '';
826         sql_where2 TEXT := '';
827         sql_where3 TEXT := '';
828         sql_where4 TEXT := '';
829         output RECORD;
830     BEGIN
831         SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
832         FOR output IN 
833             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
834         LOOP
835             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 ';
836             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);
837             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);
838             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);
839             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);
840             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,'') || ';';
841             --RAISE INFO 'sql = %', sql;
842             PERFORM migration_tools.exec( $1, sql );
843         END LOOP;
844         BEGIN
845             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
846         EXCEPTION
847             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
848         END;
849     END;
850 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
851
852 -- expand_barcode
853 --   $barcode      source barcode
854 --   $prefix       prefix to add to barcode, NULL = add no prefix
855 --   $maxlen       maximum length of barcode; default to 14 if left NULL
856 --   $pad          padding string to apply to left of source barcode before adding
857 --                 prefix and suffix; set to NULL or '' if no padding is desired
858 --   $suffix       suffix to add to barcode, NULL = add no suffix
859 --
860 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
861 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
862 --
863 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
864     my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
865
866     # default case
867     return unless defined $barcode;
868
869     $prefix     = '' unless defined $prefix;
870     $maxlen ||= 14;
871     $pad        = '0' unless defined $pad;
872     $suffix     = '' unless defined $suffix;
873
874     # bail out if adding prefix and suffix would bring new barcode over max length
875     return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
876
877     my $new_barcode = $barcode;
878     if ($pad ne '') {
879         my $pad_length = $maxlen - length($prefix) - length($suffix);
880         if (length($barcode) < $pad_length) {
881             # assuming we always want padding on the left
882             # also assuming that it is possible to have the pad string be longer than 1 character
883             $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
884         }
885     }
886
887     # bail out if adding prefix and suffix would bring new barcode over max length
888     return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
889
890     return "$prefix$new_barcode$suffix";
891 $$ LANGUAGE PLPERLU STABLE;
892
893 -- remove previous version of this function
894 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
895
896 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
897     DECLARE
898         attempt_value ALIAS FOR $1;
899         datatype ALIAS FOR $2;
900     BEGIN
901         EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
902         RETURN attempt_value;
903     EXCEPTION
904         WHEN OTHERS THEN RETURN NULL;
905     END;
906 $$ LANGUAGE PLPGSQL STRICT STABLE;
907
908 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
909     DECLARE
910         attempt_value ALIAS FOR $1;
911         fail_value ALIAS FOR $2;
912         output DATE;
913     BEGIN
914         FOR output IN
915             EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
916         LOOP
917             RETURN output;
918         END LOOP;
919     EXCEPTION
920         WHEN OTHERS THEN
921             FOR output IN
922                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
923             LOOP
924                 RETURN output;
925             END LOOP;
926     END;
927 $$ LANGUAGE PLPGSQL STRICT STABLE;
928
929 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
930     DECLARE
931         attempt_value ALIAS FOR $1;
932         fail_value ALIAS FOR $2;
933         output TIMESTAMPTZ;
934     BEGIN
935         FOR output IN
936             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
937         LOOP
938             RETURN output;
939         END LOOP;
940     EXCEPTION
941         WHEN OTHERS THEN
942             FOR output IN
943                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
944             LOOP
945                 RETURN output;
946             END LOOP;
947     END;
948 $$ LANGUAGE PLPGSQL STRICT STABLE;
949
950 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
951     DECLARE
952         attempt_value ALIAS FOR $1;
953         fail_value ALIAS FOR $2;
954         output DATE;
955     BEGIN
956         FOR output IN
957             EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
958         LOOP
959             RETURN output;
960         END LOOP;
961     EXCEPTION
962         WHEN OTHERS THEN
963             FOR output IN
964                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
965             LOOP
966                 RETURN output;
967             END LOOP;
968     END;
969 $$ LANGUAGE PLPGSQL STRICT STABLE;
970
971 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
972     DECLARE
973         attempt_value ALIAS FOR $1;
974         fail_value ALIAS FOR $2;
975         output TIMESTAMP;
976     BEGIN
977             output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
978             RETURN output;
979     EXCEPTION
980         WHEN OTHERS THEN
981             FOR output IN
982                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
983             LOOP
984                 RETURN output;
985             END LOOP;
986     END;
987 $$ LANGUAGE PLPGSQL STRICT STABLE;
988
989 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
990     DECLARE
991         attempt_value ALIAS FOR $1;
992         fail_value ALIAS FOR $2;
993         output NUMERIC(8,2);
994     BEGIN
995         FOR output IN
996             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
997         LOOP
998             RETURN output;
999         END LOOP;
1000     EXCEPTION
1001         WHEN OTHERS THEN
1002             FOR output IN
1003                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1004             LOOP
1005                 RETURN output;
1006             END LOOP;
1007     END;
1008 $$ LANGUAGE PLPGSQL STRICT STABLE;
1009
1010 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1011     DECLARE
1012         attempt_value ALIAS FOR $1;
1013         fail_value ALIAS FOR $2;
1014         output NUMERIC(6,2);
1015     BEGIN
1016         FOR output IN
1017             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1018         LOOP
1019             RETURN output;
1020         END LOOP;
1021     EXCEPTION
1022         WHEN OTHERS THEN
1023             FOR output IN
1024                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1025             LOOP
1026                 RETURN output;
1027             END LOOP;
1028     END;
1029 $$ LANGUAGE PLPGSQL STRICT STABLE;
1030
1031 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1032     DECLARE
1033         attempt_value ALIAS FOR $1;
1034         fail_value ALIAS FOR $2;
1035         output NUMERIC(8,2);
1036     BEGIN
1037         IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1038             RAISE EXCEPTION 'too many digits';
1039         END IF;
1040         FOR output IN
1041             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;'
1042         LOOP
1043             RETURN output;
1044         END LOOP;
1045     EXCEPTION
1046         WHEN OTHERS THEN
1047             FOR output IN
1048                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1049             LOOP
1050                 RETURN output;
1051             END LOOP;
1052     END;
1053 $$ LANGUAGE PLPGSQL STRICT STABLE;
1054
1055 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1056     DECLARE
1057         attempt_value ALIAS FOR $1;
1058         fail_value ALIAS FOR $2;
1059         output NUMERIC(6,2);
1060     BEGIN
1061         IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1062             RAISE EXCEPTION 'too many digits';
1063         END IF;
1064         FOR output IN
1065             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;'
1066         LOOP
1067             RETURN output;
1068         END LOOP;
1069     EXCEPTION
1070         WHEN OTHERS THEN
1071             FOR output IN
1072                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1073             LOOP
1074                 RETURN output;
1075             END LOOP;
1076     END;
1077 $$ LANGUAGE PLPGSQL STRICT STABLE;
1078
1079 -- add_codabar_checkdigit
1080 --   $barcode      source barcode
1081 --
1082 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1083 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1084 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
1085 -- input string does not meet those requirements, it is returned unchanged.
1086 --
1087 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1088     my $barcode = shift;
1089
1090     return $barcode if $barcode !~ /^\d{13,14}$/;
1091     $barcode = substr($barcode, 0, 13); # ignore 14th digit
1092     my @digits = split //, $barcode;
1093     my $total = 0;
1094     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1095     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1096     my $remainder = $total % 10;
1097     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1098     return $barcode . $checkdigit; 
1099 $$ LANGUAGE PLPERLU STRICT STABLE;
1100
1101 -- add_code39mod43_checkdigit
1102 --   $barcode      source barcode
1103 --
1104 -- If the source string is 13 or 14 characters long and contains only valid
1105 -- Code 39 mod 43 characters, adds or replaces the 14th
1106 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1107 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>.  If the
1108 -- input string does not meet those requirements, it is returned unchanged.
1109 --
1110 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1111     my $barcode = shift;
1112
1113     return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1114     $barcode = substr($barcode, 0, 13); # ignore 14th character
1115
1116     my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1117     my %nums = map { $valid_chars[$_] => $_ } (0..42);
1118
1119     my $total = 0;
1120     $total += $nums{$_} foreach split(//, $barcode);
1121     my $remainder = $total % 43;
1122     my $checkdigit = $valid_chars[$remainder];
1123     return $barcode . $checkdigit;
1124 $$ LANGUAGE PLPERLU STRICT STABLE;
1125
1126 -- add_mod16_checkdigit
1127 --   $barcode      source barcode
1128 --
1129 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1130
1131 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1132     my $barcode = shift;
1133
1134     my @digits = split //, $barcode;
1135     my $total = 0;
1136     foreach $digit (@digits) {
1137         if ($digit =~ /[0-9]/) { $total += $digit;
1138         } elsif ($digit eq '-') { $total += 10;
1139         } elsif ($digit eq '$') { $total += 11;
1140         } elsif ($digit eq ':') { $total += 12;
1141         } elsif ($digit eq '/') { $total += 13;
1142         } elsif ($digit eq '.') { $total += 14;
1143         } elsif ($digit eq '+') { $total += 15;
1144         } elsif ($digit eq 'A') { $total += 16;
1145         } elsif ($digit eq 'B') { $total += 17;
1146         } elsif ($digit eq 'C') { $total += 18;
1147         } elsif ($digit eq 'D') { $total += 19;
1148         } else { die "invalid digit <$digit>";
1149         }
1150     }
1151     my $remainder = $total % 16;
1152     my $difference = 16 - $remainder;
1153     my $checkdigit;
1154     if ($difference < 10) { $checkdigit = $difference;
1155     } elsif ($difference == 10) { $checkdigit = '-';
1156     } elsif ($difference == 11) { $checkdigit = '$';
1157     } elsif ($difference == 12) { $checkdigit = ':';
1158     } elsif ($difference == 13) { $checkdigit = '/';
1159     } elsif ($difference == 14) { $checkdigit = '.';
1160     } elsif ($difference == 15) { $checkdigit = '+';
1161     } else { die "error calculating checkdigit";
1162     }
1163
1164     return $barcode . $checkdigit;
1165 $$ LANGUAGE PLPERLU STRICT STABLE;
1166
1167 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1168   DECLARE
1169     phone TEXT := $1;
1170     areacode TEXT := $2;
1171     temp TEXT := '';
1172     output TEXT := '';
1173     n_digits INTEGER := 0;
1174   BEGIN
1175     temp := phone;
1176     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1177     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1178     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1179     IF n_digits = 7 AND areacode <> '' THEN
1180       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1181       output := (areacode || '-' || temp);
1182     ELSE
1183       output := temp;
1184     END IF;
1185     RETURN output;
1186   END;
1187
1188 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1189
1190 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1191   my ($marcxml, $pos, $value) = @_;
1192
1193   use MARC::Record;
1194   use MARC::File::XML;
1195
1196   my $xml = $marcxml;
1197   eval {
1198     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1199     my $leader = $marc->leader();
1200     substr($leader, $pos, 1) = $value;
1201     $marc->leader($leader);
1202     $xml = $marc->as_xml_record;
1203     $xml =~ s/^<\?.+?\?>$//mo;
1204     $xml =~ s/\n//sgo;
1205     $xml =~ s/>\s+</></sgo;
1206   };
1207   return $xml;
1208 $$ LANGUAGE PLPERLU STABLE;
1209
1210 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1211   my ($marcxml, $pos, $value) = @_;
1212
1213   use MARC::Record;
1214   use MARC::File::XML;
1215
1216   my $xml = $marcxml;
1217   eval {
1218     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1219     my $f008 = $marc->field('008');
1220
1221     if ($f008) {
1222        my $field = $f008->data();
1223        substr($field, $pos, 1) = $value;
1224        $f008->update($field);
1225        $xml = $marc->as_xml_record;
1226        $xml =~ s/^<\?.+?\?>$//mo;
1227        $xml =~ s/\n//sgo;
1228        $xml =~ s/>\s+</></sgo;
1229     }
1230   };
1231   return $xml;
1232 $$ LANGUAGE PLPERLU STABLE;
1233
1234
1235 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1236   DECLARE
1237     profile ALIAS FOR $1;
1238   BEGIN
1239     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1240   END;
1241 $$ LANGUAGE PLPGSQL STRICT STABLE;
1242
1243
1244 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1245   BEGIN
1246     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1247   END;
1248 $$ LANGUAGE PLPGSQL STRICT STABLE;
1249
1250
1251 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1252
1253   my ($marcxml, $tags) = @_;
1254
1255   use MARC::Record;
1256   use MARC::File::XML;
1257
1258   my $xml = $marcxml;
1259
1260   eval {
1261     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1262     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1263
1264     my @incumbents = ();
1265
1266     foreach my $field ( $marc->fields() ) {
1267       push @incumbents, $field->as_formatted();
1268     }
1269
1270     foreach $field ( $to_insert->fields() ) {
1271       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1272         $marc->insert_fields_ordered( ($field) );
1273       }
1274     }
1275
1276     $xml = $marc->as_xml_record;
1277     $xml =~ s/^<\?.+?\?>$//mo;
1278     $xml =~ s/\n//sgo;
1279     $xml =~ s/>\s+</></sgo;
1280   };
1281
1282   return $xml;
1283
1284 $$ LANGUAGE PLPERLU STABLE;
1285
1286 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1287
1288 -- Usage:
1289 --
1290 --   First make sure the circ matrix is loaded and the circulations
1291 --   have been staged to the extent possible (but at the very least
1292 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1293 --   circ modifiers must also be in place.
1294 --
1295 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1296 --
1297
1298 DECLARE
1299   circ_lib             INT;
1300   target_copy          INT;
1301   usr                  INT;
1302   is_renewal           BOOLEAN;
1303   this_duration_rule   INT;
1304   this_fine_rule       INT;
1305   this_max_fine_rule   INT;
1306   rcd                  config.rule_circ_duration%ROWTYPE;
1307   rrf                  config.rule_recurring_fine%ROWTYPE;
1308   rmf                  config.rule_max_fine%ROWTYPE;
1309   circ                 INT;
1310   n                    INT := 0;
1311   n_circs              INT;
1312   
1313 BEGIN
1314
1315   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1316
1317   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1318
1319     -- Fetch the correct rules for this circulation
1320     EXECUTE ('
1321       SELECT
1322         circ_lib,
1323         target_copy,
1324         usr,
1325         CASE
1326           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1327           ELSE FALSE
1328         END
1329       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1330       INTO circ_lib, target_copy, usr, is_renewal ;
1331     SELECT
1332       INTO this_duration_rule,
1333            this_fine_rule,
1334            this_max_fine_rule
1335       duration_rule,
1336       recurring_fine_rule,
1337       max_fine_rule
1338       FROM action.item_user_circ_test(
1339         circ_lib,
1340         target_copy,
1341         usr,
1342         is_renewal
1343         );
1344     SELECT INTO rcd * FROM config.rule_circ_duration
1345       WHERE id = this_duration_rule;
1346     SELECT INTO rrf * FROM config.rule_recurring_fine
1347       WHERE id = this_fine_rule;
1348     SELECT INTO rmf * FROM config.rule_max_fine
1349       WHERE id = this_max_fine_rule;
1350
1351     -- Apply the rules to this circulation
1352     EXECUTE ('UPDATE ' || tablename || ' c
1353     SET
1354       duration_rule = rcd.name,
1355       recurring_fine_rule = rrf.name,
1356       max_fine_rule = rmf.name,
1357       duration = rcd.normal,
1358       recurring_fine = rrf.normal,
1359       max_fine =
1360         CASE rmf.is_percent
1361           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1362           ELSE rmf.amount
1363         END,
1364       renewal_remaining = rcd.max_renewals
1365     FROM
1366       config.rule_circ_duration rcd,
1367       config.rule_recurring_fine rrf,
1368       config.rule_max_fine rmf,
1369                         asset.copy ac
1370     WHERE
1371       rcd.id = ' || this_duration_rule || ' AND
1372       rrf.id = ' || this_fine_rule || ' AND
1373       rmf.id = ' || this_max_fine_rule || ' AND
1374                         ac.id = c.target_copy AND
1375       c.id = ' || circ || ';');
1376
1377     -- Keep track of where we are in the process
1378     n := n + 1;
1379     IF (n % 100 = 0) THEN
1380       RAISE INFO '%', n || ' of ' || n_circs
1381         || ' (' || (100*n/n_circs) || '%) circs updated.';
1382     END IF;
1383
1384   END LOOP;
1385
1386   RETURN;
1387 END;
1388
1389 $$ LANGUAGE plpgsql;
1390
1391 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1392
1393 -- Usage:
1394 --
1395 --   First make sure the circ matrix is loaded and the circulations
1396 --   have been staged to the extent possible (but at the very least
1397 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1398 --   circ modifiers must also be in place.
1399 --
1400 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1401 --
1402
1403 DECLARE
1404   circ_lib             INT;
1405   target_copy          INT;
1406   usr                  INT;
1407   is_renewal           BOOLEAN;
1408   this_duration_rule   INT;
1409   this_fine_rule       INT;
1410   this_max_fine_rule   INT;
1411   rcd                  config.rule_circ_duration%ROWTYPE;
1412   rrf                  config.rule_recurring_fine%ROWTYPE;
1413   rmf                  config.rule_max_fine%ROWTYPE;
1414   circ                 INT;
1415   n                    INT := 0;
1416   n_circs              INT;
1417   
1418 BEGIN
1419
1420   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1421
1422   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1423
1424     -- Fetch the correct rules for this circulation
1425     EXECUTE ('
1426       SELECT
1427         circ_lib,
1428         target_copy,
1429         usr,
1430         CASE
1431           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1432           ELSE FALSE
1433         END
1434       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1435       INTO circ_lib, target_copy, usr, is_renewal ;
1436     SELECT
1437       INTO this_duration_rule,
1438            this_fine_rule,
1439            this_max_fine_rule
1440       duration_rule,
1441       recuring_fine_rule,
1442       max_fine_rule
1443       FROM action.find_circ_matrix_matchpoint(
1444         circ_lib,
1445         target_copy,
1446         usr,
1447         is_renewal
1448         );
1449     SELECT INTO rcd * FROM config.rule_circ_duration
1450       WHERE id = this_duration_rule;
1451     SELECT INTO rrf * FROM config.rule_recurring_fine
1452       WHERE id = this_fine_rule;
1453     SELECT INTO rmf * FROM config.rule_max_fine
1454       WHERE id = this_max_fine_rule;
1455
1456     -- Apply the rules to this circulation
1457     EXECUTE ('UPDATE ' || tablename || ' c
1458     SET
1459       duration_rule = rcd.name,
1460       recuring_fine_rule = rrf.name,
1461       max_fine_rule = rmf.name,
1462       duration = rcd.normal,
1463       recuring_fine = rrf.normal,
1464       max_fine =
1465         CASE rmf.is_percent
1466           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1467           ELSE rmf.amount
1468         END,
1469       renewal_remaining = rcd.max_renewals
1470     FROM
1471       config.rule_circ_duration rcd,
1472       config.rule_recuring_fine rrf,
1473       config.rule_max_fine rmf,
1474                         asset.copy ac
1475     WHERE
1476       rcd.id = ' || this_duration_rule || ' AND
1477       rrf.id = ' || this_fine_rule || ' AND
1478       rmf.id = ' || this_max_fine_rule || ' AND
1479                         ac.id = c.target_copy AND
1480       c.id = ' || circ || ';');
1481
1482     -- Keep track of where we are in the process
1483     n := n + 1;
1484     IF (n % 100 = 0) THEN
1485       RAISE INFO '%', n || ' of ' || n_circs
1486         || ' (' || (100*n/n_circs) || '%) circs updated.';
1487     END IF;
1488
1489   END LOOP;
1490
1491   RETURN;
1492 END;
1493
1494 $$ LANGUAGE plpgsql;
1495
1496 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
1497
1498 -- Usage:
1499 --
1500 --   First make sure the circ matrix is loaded and the circulations
1501 --   have been staged to the extent possible (but at the very least
1502 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1503 --   circ modifiers must also be in place.
1504 --
1505 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1506 --
1507
1508 DECLARE
1509   circ_lib             INT;
1510   target_copy          INT;
1511   usr                  INT;
1512   is_renewal           BOOLEAN;
1513   this_duration_rule   INT;
1514   this_fine_rule       INT;
1515   this_max_fine_rule   INT;
1516   rcd                  config.rule_circ_duration%ROWTYPE;
1517   rrf                  config.rule_recurring_fine%ROWTYPE;
1518   rmf                  config.rule_max_fine%ROWTYPE;
1519   circ                 INT;
1520   n                    INT := 0;
1521   n_circs              INT;
1522   
1523 BEGIN
1524
1525   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1526
1527   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1528
1529     -- Fetch the correct rules for this circulation
1530     EXECUTE ('
1531       SELECT
1532         circ_lib,
1533         target_copy,
1534         usr,
1535         CASE
1536           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1537           ELSE FALSE
1538         END
1539       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1540       INTO circ_lib, target_copy, usr, is_renewal ;
1541     SELECT
1542       INTO this_duration_rule,
1543            this_fine_rule,
1544            this_max_fine_rule
1545       (matchpoint).duration_rule,
1546       (matchpoint).recurring_fine_rule,
1547       (matchpoint).max_fine_rule
1548       FROM action.find_circ_matrix_matchpoint(
1549         circ_lib,
1550         target_copy,
1551         usr,
1552         is_renewal
1553         );
1554     SELECT INTO rcd * FROM config.rule_circ_duration
1555       WHERE id = this_duration_rule;
1556     SELECT INTO rrf * FROM config.rule_recurring_fine
1557       WHERE id = this_fine_rule;
1558     SELECT INTO rmf * FROM config.rule_max_fine
1559       WHERE id = this_max_fine_rule;
1560
1561     -- Apply the rules to this circulation
1562     EXECUTE ('UPDATE ' || tablename || ' c
1563     SET
1564       duration_rule = rcd.name,
1565       recurring_fine_rule = rrf.name,
1566       max_fine_rule = rmf.name,
1567       duration = rcd.normal,
1568       recurring_fine = rrf.normal,
1569       max_fine =
1570         CASE rmf.is_percent
1571           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1572           ELSE rmf.amount
1573         END,
1574       renewal_remaining = rcd.max_renewals,
1575       grace_period = rrf.grace_period
1576     FROM
1577       config.rule_circ_duration rcd,
1578       config.rule_recurring_fine rrf,
1579       config.rule_max_fine rmf,
1580                         asset.copy ac
1581     WHERE
1582       rcd.id = ' || this_duration_rule || ' AND
1583       rrf.id = ' || this_fine_rule || ' AND
1584       rmf.id = ' || this_max_fine_rule || ' AND
1585                         ac.id = c.target_copy AND
1586       c.id = ' || circ || ';');
1587
1588     -- Keep track of where we are in the process
1589     n := n + 1;
1590     IF (n % 100 = 0) THEN
1591       RAISE INFO '%', n || ' of ' || n_circs
1592         || ' (' || (100*n/n_circs) || '%) circs updated.';
1593     END IF;
1594
1595   END LOOP;
1596
1597   RETURN;
1598 END;
1599
1600 $$ LANGUAGE plpgsql;
1601
1602 -- TODO: make another version of the procedure below that can work with specified copy staging tables
1603 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
1604 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
1605 DECLARE
1606     context_lib             INT;
1607     charge_lost_on_zero     BOOLEAN;
1608     min_price               NUMERIC;
1609     max_price               NUMERIC;
1610     default_price           NUMERIC;
1611     working_price           NUMERIC;
1612
1613 BEGIN
1614
1615     SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
1616         FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
1617
1618     SELECT INTO charge_lost_on_zero value
1619         FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
1620
1621     SELECT INTO min_price value
1622         FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
1623
1624     SELECT INTO max_price value
1625         FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
1626
1627     SELECT INTO default_price value
1628         FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
1629
1630     SELECT INTO working_price price FROM asset.copy WHERE id = item;
1631
1632     IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
1633         working_price := default_price;
1634     END IF;
1635
1636     IF (max_price IS NOT NULL AND working_price > max_price) THEN
1637         working_price := max_price;
1638     END IF;
1639
1640     IF (min_price IS NOT NULL AND working_price < min_price) THEN
1641         IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
1642             working_price := min_price;
1643         END IF;
1644     END IF;
1645
1646     RETURN working_price;
1647
1648 END;
1649
1650 $$ LANGUAGE plpgsql;
1651
1652 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
1653
1654 -- Usage:
1655 --
1656 --   First make sure the circ matrix is loaded and the circulations
1657 --   have been staged to the extent possible (but at the very least
1658 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1659 --   circ modifiers must also be in place.
1660 --
1661 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
1662 --
1663
1664 DECLARE
1665   circ_lib             INT;
1666   target_copy          INT;
1667   usr                  INT;
1668   is_renewal           BOOLEAN;
1669   this_duration_rule   INT;
1670   this_fine_rule       INT;
1671   this_max_fine_rule   INT;
1672   rcd                  config.rule_circ_duration%ROWTYPE;
1673   rrf                  config.rule_recurring_fine%ROWTYPE;
1674   rmf                  config.rule_max_fine%ROWTYPE;
1675   n                    INT := 0;
1676   n_circs              INT := 1;
1677   
1678 BEGIN
1679
1680   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1681
1682   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1683
1684     -- Fetch the correct rules for this circulation
1685     EXECUTE ('
1686       SELECT
1687         circ_lib,
1688         target_copy,
1689         usr,
1690         CASE
1691           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1692           ELSE FALSE
1693         END
1694       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1695       INTO circ_lib, target_copy, usr, is_renewal ;
1696     SELECT
1697       INTO this_duration_rule,
1698            this_fine_rule,
1699            this_max_fine_rule
1700       (matchpoint).duration_rule,
1701       (matchpoint).recurring_fine_rule,
1702       (matchpoint).max_fine_rule
1703       FROM action.find_circ_matrix_matchpoint(
1704         circ_lib,
1705         target_copy,
1706         usr,
1707         is_renewal
1708         );
1709     SELECT INTO rcd * FROM config.rule_circ_duration
1710       WHERE id = this_duration_rule;
1711     SELECT INTO rrf * FROM config.rule_recurring_fine
1712       WHERE id = this_fine_rule;
1713     SELECT INTO rmf * FROM config.rule_max_fine
1714       WHERE id = this_max_fine_rule;
1715
1716     -- Apply the rules to this circulation
1717     EXECUTE ('UPDATE ' || tablename || ' c
1718     SET
1719       duration_rule = rcd.name,
1720       recurring_fine_rule = rrf.name,
1721       max_fine_rule = rmf.name,
1722       duration = rcd.normal,
1723       recurring_fine = rrf.normal,
1724       max_fine =
1725         CASE rmf.is_percent
1726           WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
1727           ELSE rmf.amount
1728         END,
1729       renewal_remaining = rcd.max_renewals,
1730       grace_period = rrf.grace_period
1731     FROM
1732       config.rule_circ_duration rcd,
1733       config.rule_recurring_fine rrf,
1734       config.rule_max_fine rmf,
1735                         asset.copy ac
1736     WHERE
1737       rcd.id = ' || this_duration_rule || ' AND
1738       rrf.id = ' || this_fine_rule || ' AND
1739       rmf.id = ' || this_max_fine_rule || ' AND
1740                         ac.id = c.target_copy AND
1741       c.id = ' || circ || ';');
1742
1743     -- Keep track of where we are in the process
1744     n := n + 1;
1745     IF (n % 100 = 0) THEN
1746       RAISE INFO '%', n || ' of ' || n_circs
1747         || ' (' || (100*n/n_circs) || '%) circs updated.';
1748     END IF;
1749
1750   --END LOOP;
1751
1752   RETURN;
1753 END;
1754
1755 $$ LANGUAGE plpgsql;
1756
1757
1758
1759
1760 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
1761
1762 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
1763 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
1764
1765 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
1766 -- TODO: Add a similar tool for actor stat cats, which behave differently.
1767
1768 DECLARE
1769         c                    TEXT := schemaname || '.asset_copy_legacy';
1770         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
1771         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
1772         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
1773         stat_cat                                                 INT;
1774   stat_cat_entry       INT;
1775   
1776 BEGIN
1777
1778   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
1779
1780                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
1781
1782                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
1783                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
1784                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
1785
1786   END LOOP;
1787
1788   RETURN;
1789 END;
1790
1791 $$ LANGUAGE plpgsql;
1792
1793 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
1794
1795 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
1796 --        This will assign standing penalties as needed.
1797
1798 DECLARE
1799   org_unit  INT;
1800   usr       INT;
1801
1802 BEGIN
1803
1804   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
1805
1806     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
1807   
1808       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
1809
1810     END LOOP;
1811
1812   END LOOP;
1813
1814   RETURN;
1815
1816 END;
1817
1818 $$ LANGUAGE plpgsql;
1819
1820
1821 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1822
1823 BEGIN
1824   INSERT INTO metabib.metarecord (fingerprint, master_record)
1825     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1826       FROM  biblio.record_entry b
1827       WHERE NOT b.deleted
1828         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)
1829         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1830       ORDER BY b.fingerprint, b.quality DESC;
1831   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1832     SELECT  m.id, r.id
1833       FROM  biblio.record_entry r
1834       JOIN  metabib.metarecord m USING (fingerprint)
1835      WHERE  NOT r.deleted;
1836 END;
1837   
1838 $$ LANGUAGE plpgsql;
1839
1840
1841 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1842
1843 BEGIN
1844   INSERT INTO metabib.metarecord (fingerprint, master_record)
1845     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1846       FROM  biblio.record_entry b
1847       WHERE NOT b.deleted
1848         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)
1849         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1850       ORDER BY b.fingerprint, b.quality DESC;
1851   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1852     SELECT  m.id, r.id
1853       FROM  biblio.record_entry r
1854         JOIN metabib.metarecord m USING (fingerprint)
1855       WHERE NOT r.deleted
1856         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);
1857 END;
1858     
1859 $$ LANGUAGE plpgsql;
1860
1861
1862 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1863
1864 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1865 --        Then SELECT migration_tools.create_cards('m_foo');
1866
1867 DECLARE
1868         u                    TEXT := schemaname || '.actor_usr_legacy';
1869         c                    TEXT := schemaname || '.actor_card';
1870   
1871 BEGIN
1872
1873         EXECUTE ('DELETE FROM ' || c || ';');
1874         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1875         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1876
1877   RETURN;
1878
1879 END;
1880
1881 $$ LANGUAGE plpgsql;
1882
1883
1884 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
1885
1886   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1887
1888   my ($marcxml, $shortname) = @_;
1889
1890   use MARC::Record;
1891   use MARC::File::XML;
1892
1893   my $xml = $marcxml;
1894
1895   eval {
1896     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1897
1898     foreach my $field ( $marc->field('856') ) {
1899       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1900            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1901         $field->add_subfields( '9' => $shortname );
1902                                 $field->update( ind2 => '0');
1903       }
1904     }
1905
1906     $xml = $marc->as_xml_record;
1907     $xml =~ s/^<\?.+?\?>$//mo;
1908     $xml =~ s/\n//sgo;
1909     $xml =~ s/>\s+</></sgo;
1910   };
1911
1912   return $xml;
1913
1914 $$ LANGUAGE PLPERLU STABLE;
1915
1916 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1917
1918   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1919
1920   my ($marcxml, $shortname) = @_;
1921
1922   use MARC::Record;
1923   use MARC::File::XML;
1924
1925   my $xml = $marcxml;
1926
1927   eval {
1928     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1929
1930     foreach my $field ( $marc->field('856') ) {
1931       if ( ! $field->as_string('9') ) {
1932         $field->add_subfields( '9' => $shortname );
1933       }
1934     }
1935
1936     $xml = $marc->as_xml_record;
1937     $xml =~ s/^<\?.+?\?>$//mo;
1938     $xml =~ s/\n//sgo;
1939     $xml =~ s/>\s+</></sgo;
1940   };
1941
1942   return $xml;
1943
1944 $$ LANGUAGE PLPERLU STABLE;
1945
1946
1947 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
1948
1949 DECLARE
1950   old_volume   BIGINT;
1951   new_volume   BIGINT;
1952   bib          BIGINT;
1953   owner        INTEGER;
1954   old_label    TEXT;
1955   remainder    BIGINT;
1956
1957 BEGIN
1958
1959   -- Bail out if asked to change the label to ##URI##
1960   IF new_label = '##URI##' THEN
1961     RETURN;
1962   END IF;
1963
1964   -- Gather information
1965   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1966   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1967   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1968
1969   -- Bail out if the label already is ##URI##
1970   IF old_label = '##URI##' THEN
1971     RETURN;
1972   END IF;
1973
1974   -- Bail out if the call number label is already correct
1975   IF new_volume = old_volume THEN
1976     RETURN;
1977   END IF;
1978
1979   -- Check whether we already have a destination volume available
1980   SELECT id INTO new_volume FROM asset.call_number 
1981     WHERE 
1982       record = bib AND
1983       owning_lib = owner AND
1984       label = new_label AND
1985       NOT deleted;
1986
1987   -- Create destination volume if needed
1988   IF NOT FOUND THEN
1989     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class) 
1990       VALUES (1, 1, bib, owner, new_label, cn_class);
1991     SELECT id INTO new_volume FROM asset.call_number
1992       WHERE 
1993         record = bib AND
1994         owning_lib = owner AND
1995         label = new_label AND
1996         NOT deleted;
1997   END IF;
1998
1999   -- Move copy to destination
2000   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2001
2002   -- Delete source volume if it is now empty
2003   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2004   IF NOT FOUND THEN
2005     DELETE FROM asset.call_number WHERE id = old_volume;
2006   END IF;
2007
2008 END;
2009
2010 $$ LANGUAGE plpgsql;
2011
2012 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2013
2014         my $input = $_[0];
2015         my %zipdata;
2016
2017         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2018
2019         while (<FH>) {
2020                 chomp;
2021                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2022                 $zipdata{$zip} = [$city, $state, $county];
2023         }
2024
2025         if (defined $zipdata{$input}) {
2026                 my ($city, $state, $county) = @{$zipdata{$input}};
2027                 return [$city, $state, $county];
2028         } elsif (defined $zipdata{substr $input, 0, 5}) {
2029                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2030                 return [$city, $state, $county];
2031         } else {
2032                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2033         }
2034   
2035 $$ LANGUAGE PLPERLU STABLE;
2036
2037 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2038
2039 DECLARE
2040   ou  INT;
2041         org_unit_depth INT;
2042         ou_parent INT;
2043         parent_depth INT;
2044   errors_found BOOLEAN;
2045         ou_shortname TEXT;
2046         parent_shortname TEXT;
2047         ou_type_name TEXT;
2048         parent_type TEXT;
2049         type_id INT;
2050         type_depth INT;
2051         type_parent INT;
2052         type_parent_depth INT;
2053         proper_parent TEXT;
2054
2055 BEGIN
2056
2057         errors_found := FALSE;
2058
2059 -- Checking actor.org_unit_type
2060
2061         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2062
2063                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2064                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2065
2066                 IF type_parent IS NOT NULL THEN
2067
2068                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2069
2070                         IF type_depth - type_parent_depth <> 1 THEN
2071                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2072                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2073                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2074                                         ou_type_name, type_depth, parent_type, type_parent_depth;
2075                                 errors_found := TRUE;
2076
2077                         END IF;
2078
2079                 END IF;
2080
2081         END LOOP;
2082
2083 -- Checking actor.org_unit
2084
2085   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2086
2087                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2088                 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;
2089                 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;
2090                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2091                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2092                 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;
2093                 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;
2094
2095                 IF ou_parent IS NOT NULL THEN
2096
2097                         IF      (org_unit_depth - parent_depth <> 1) OR (
2098                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2099                         ) THEN
2100                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).', 
2101                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2102                                 errors_found := TRUE;
2103                         END IF;
2104
2105                 END IF;
2106
2107   END LOOP;
2108
2109         IF NOT errors_found THEN
2110                 RAISE INFO 'No errors found.';
2111         END IF;
2112
2113   RETURN;
2114
2115 END;
2116
2117 $$ LANGUAGE plpgsql;
2118
2119
2120 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2121
2122 BEGIN   
2123
2124         DELETE FROM asset.opac_visible_copies;
2125
2126         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2127                 SELECT DISTINCT
2128                         cp.id, cp.circ_lib, cn.record
2129                 FROM
2130                         asset.copy cp
2131                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
2132                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2133                         JOIN asset.copy_location cl ON (cp.location = cl.id)
2134                         JOIN config.copy_status cs ON (cp.status = cs.id)
2135                         JOIN biblio.record_entry b ON (cn.record = b.id)
2136                 WHERE 
2137                         NOT cp.deleted AND
2138                         NOT cn.deleted AND
2139                         NOT b.deleted AND
2140                         cs.opac_visible AND
2141                         cl.opac_visible AND
2142                         cp.opac_visible AND
2143                         a.opac_visible AND
2144                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2145
2146 END;
2147
2148 $$ LANGUAGE plpgsql;
2149
2150
2151 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2152
2153 DECLARE
2154   old_volume     BIGINT;
2155   new_volume     BIGINT;
2156   bib            BIGINT;
2157   old_owning_lib INTEGER;
2158         old_label      TEXT;
2159   remainder      BIGINT;
2160
2161 BEGIN
2162
2163   -- Gather information
2164   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2165   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2166   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2167
2168         -- Bail out if the new_owning_lib is not the ID of an org_unit
2169         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2170                 RAISE WARNING 
2171                         '% is not a valid actor.org_unit ID; no change made.', 
2172                                 new_owning_lib;
2173                 RETURN;
2174         END IF;
2175
2176   -- Bail out discreetly if the owning_lib is already correct
2177   IF new_owning_lib = old_owning_lib THEN
2178     RETURN;
2179   END IF;
2180
2181   -- Check whether we already have a destination volume available
2182   SELECT id INTO new_volume FROM asset.call_number 
2183     WHERE 
2184       record = bib AND
2185       owning_lib = new_owning_lib AND
2186       label = old_label AND
2187       NOT deleted;
2188
2189   -- Create destination volume if needed
2190   IF NOT FOUND THEN
2191     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) 
2192       VALUES (1, 1, bib, new_owning_lib, old_label);
2193     SELECT id INTO new_volume FROM asset.call_number
2194       WHERE 
2195         record = bib AND
2196         owning_lib = new_owning_lib AND
2197         label = old_label AND
2198         NOT deleted;
2199   END IF;
2200
2201   -- Move copy to destination
2202   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2203
2204   -- Delete source volume if it is now empty
2205   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2206   IF NOT FOUND THEN
2207     DELETE FROM asset.call_number WHERE id = old_volume;
2208   END IF;
2209
2210 END;
2211
2212 $$ LANGUAGE plpgsql;
2213
2214
2215 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2216
2217 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2218
2219 DECLARE
2220         new_owning_lib  INTEGER;
2221
2222 BEGIN
2223
2224         -- Parse the new_owner as an org unit ID or shortname
2225         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2226                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2227                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2228         ELSIF new_owner ~ E'^[0-9]+$' THEN
2229                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2230                         RAISE INFO 
2231                                 '%',
2232                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
2233                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2234                         new_owning_lib := new_owner::INTEGER;
2235                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2236                 END IF;
2237         ELSE
2238                 RAISE WARNING 
2239                         '% is not a valid actor.org_unit shortname or ID; no change made.', 
2240                         new_owning_lib;
2241                 RETURN;
2242         END IF;
2243
2244 END;
2245
2246 $$ LANGUAGE plpgsql;
2247
2248 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2249
2250 use MARC::Record;
2251 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2252 use MARC::Charset;
2253
2254 MARC::Charset->assume_unicode(1);
2255
2256 my $xml = shift;
2257
2258 eval {
2259     my $r = MARC::Record->new_from_xml( $xml );
2260     my $output_xml = $r->as_xml_record();
2261 };
2262 if ($@) {
2263     return 0;
2264 } else {
2265     return 1;
2266 }
2267
2268 $func$ LANGUAGE PLPERLU;
2269 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2270
2271 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2272 BEGIN
2273    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2274            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2275            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
2276    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2277            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2278            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
2279    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2280            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2281            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
2282    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2283            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2284            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
2285    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2286            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2287            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2288    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2289            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2290            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
2291    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2292            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2293            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
2294    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
2295    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
2296    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
2297    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
2298    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
2299 END;
2300 $FUNC$ LANGUAGE PLPGSQL;
2301
2302 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2303 BEGIN
2304    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
2305    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
2306    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
2307    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
2308    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2309    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
2310    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
2311
2312    -- import any new circ rules
2313    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2314    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2315    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2316    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2317
2318    -- and permission groups
2319    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2320
2321 END;
2322 $FUNC$ LANGUAGE PLPGSQL;
2323
2324
2325 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$
2326 DECLARE
2327     name TEXT;
2328     loopq TEXT;
2329     existsq TEXT;
2330     ct INTEGER;
2331     cols TEXT[];
2332     copyst TEXT;
2333 BEGIN
2334     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2335     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2336     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
2337     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2338     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2339     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2340     FOR name IN EXECUTE loopq LOOP
2341        EXECUTE existsq INTO ct USING name;
2342        IF ct = 0 THEN
2343            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2344            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || 
2345                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2346            EXECUTE copyst USING name;
2347        END IF;
2348     END LOOP;
2349 END;
2350 $FUNC$ LANGUAGE PLPGSQL;
2351
2352 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
2353 DECLARE
2354     id BIGINT;
2355     loopq TEXT;
2356     cols TEXT[];
2357     splitst TEXT;
2358 BEGIN
2359     loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
2360     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> 'id' AND column_name <> matchcol;
2361     FOR id IN EXECUTE loopq USING delimiter LOOP
2362        RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
2363        splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
2364                  ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
2365        EXECUTE splitst USING id, delimiter;
2366     END LOOP;
2367 END;
2368 $FUNC$ LANGUAGE PLPGSQL;
2369
2370 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2371
2372 use strict;
2373 use warnings;
2374
2375 use MARC::Record;
2376 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2377 use MARC::Charset;
2378
2379 MARC::Charset->assume_unicode(1);
2380
2381 my $target_xml = shift;
2382 my $source_xml = shift;
2383 my $tags = shift;
2384
2385 my $target;
2386 my $source;
2387
2388 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2389 if ($@) {
2390     return;
2391 }
2392 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2393 if ($@) {
2394     return;
2395 }
2396
2397 my $source_id = $source->subfield('901', 'c');
2398 $source_id = $source->subfield('903', 'a') unless $source_id;
2399 my $target_id = $target->subfield('901', 'c');
2400 $target_id = $target->subfield('903', 'a') unless $target_id;
2401
2402 my %existing_fields;
2403 foreach my $tag (@$tags) {
2404     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2405     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2406     $target->insert_fields_ordered(map { $_->clone() } @to_add);
2407     if (@to_add) {
2408         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2409     }
2410 }
2411
2412 my $xml = $target->as_xml_record;
2413 $xml =~ s/^<\?.+?\?>$//mo;
2414 $xml =~ s/\n//sgo;
2415 $xml =~ s/>\s+</></sgo;
2416
2417 return $xml;
2418
2419 $func$ LANGUAGE PLPERLU;
2420 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.';
2421
2422 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2423
2424 use strict;
2425 use warnings;
2426
2427 use MARC::Record;
2428 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2429 use Text::CSV;
2430
2431 my $in_tags = shift;
2432 my $in_values = shift;
2433
2434 # hack-and-slash parsing of array-passed-as-string;
2435 # this can go away once everybody is running Postgres 9.1+
2436 my $csv = Text::CSV->new({binary => 1});
2437 $in_tags =~ s/^{//;
2438 $in_tags =~ s/}$//;
2439 my $status = $csv->parse($in_tags);
2440 my $tags = [ $csv->fields() ];
2441 $in_values =~ s/^{//;
2442 $in_values =~ s/}$//;
2443 $status = $csv->parse($in_values);
2444 my $values = [ $csv->fields() ];
2445
2446 my $marc = MARC::Record->new();
2447
2448 $marc->leader('00000nam a22000007  4500');
2449 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
2450
2451 foreach my $i (0..$#$tags) {
2452     my ($tag, $sf);
2453     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
2454         $tag = $1;
2455         $sf = $2;
2456         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2457     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
2458         $tag = $1;
2459         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
2460     }
2461 }
2462
2463 my $xml = $marc->as_xml_record;
2464 $xml =~ s/^<\?.+?\?>$//mo;
2465 $xml =~ s/\n//sgo;
2466 $xml =~ s/>\s+</></sgo;
2467
2468 return $xml;
2469
2470 $func$ LANGUAGE PLPERLU;
2471 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
2472 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
2473 The second argument is an array of text containing the values to plug into each field.  
2474 If the value for a given field is NULL or the empty string, it is not inserted.
2475 $$;
2476
2477 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
2478
2479 my ($marcxml, $tag, $pos, $value) = @_;
2480
2481 use MARC::Record;
2482 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2483 use MARC::Charset;
2484 use strict;
2485
2486 MARC::Charset->assume_unicode(1);
2487
2488 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
2489 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
2490 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
2491 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
2492
2493 my $xml = $marcxml;
2494 eval {
2495     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2496
2497     foreach my $field ($marc->field($tag)) {
2498         $field->update("ind$pos" => $value);
2499     }
2500     $xml = $marc->as_xml_record;
2501     $xml =~ s/^<\?.+?\?>$//mo;
2502     $xml =~ s/\n//sgo;
2503     $xml =~ s/>\s+</></sgo;
2504 };
2505 return $xml;
2506
2507 $func$ LANGUAGE PLPERLU;
2508
2509 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
2510 The first argument is a MARCXML string.
2511 The second argument is a MARC tag.
2512 The third argument is the indicator position, either 1 or 2.
2513 The fourth argument is the character to set the indicator value to.
2514 All occurences of the specified field will be changed.
2515 The function returns the revised MARCXML string.$$;
2516
2517 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
2518     username TEXT,
2519     password TEXT,
2520     org TEXT,
2521     perm_group TEXT,
2522     first_name TEXT DEFAULT '',
2523     last_name TEXT DEFAULT ''
2524 ) RETURNS VOID AS $func$
2525 BEGIN
2526     RAISE NOTICE '%', org ;
2527     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
2528     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
2529     FROM   actor.org_unit aou, permission.grp_tree pgt
2530     WHERE  aou.shortname = org
2531     AND    pgt.name = perm_group;
2532 END
2533 $func$
2534 LANGUAGE PLPGSQL;
2535
2536 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
2537 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
2538     DECLARE
2539         target_event_def ALIAS FOR $1;
2540         orgs ALIAS FOR $2;
2541     BEGIN
2542         DROP TABLE IF EXISTS new_atevdefs;
2543         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2544         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2545             INSERT INTO action_trigger.event_definition (
2546                 active
2547                 ,owner
2548                 ,name
2549                 ,hook
2550                 ,validator
2551                 ,reactor
2552                 ,cleanup_success
2553                 ,cleanup_failure
2554                 ,delay
2555                 ,max_delay
2556                 ,usr_field
2557                 ,opt_in_setting
2558                 ,delay_field
2559                 ,group_field
2560                 ,template
2561                 ,granularity
2562                 ,repeat_delay
2563             ) SELECT
2564                 'f'
2565                 ,orgs[i]
2566                 ,name || ' (clone of '||target_event_def||')'
2567                 ,hook
2568                 ,validator
2569                 ,reactor
2570                 ,cleanup_success
2571                 ,cleanup_failure
2572                 ,delay
2573                 ,max_delay
2574                 ,usr_field
2575                 ,opt_in_setting
2576                 ,delay_field
2577                 ,group_field
2578                 ,template
2579                 ,granularity
2580                 ,repeat_delay
2581             FROM
2582                 action_trigger.event_definition
2583             WHERE
2584                 id = target_event_def
2585             ;
2586             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2587             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2588             INSERT INTO action_trigger.environment (
2589                 event_def
2590                 ,path
2591                 ,collector
2592                 ,label
2593             ) SELECT
2594                 currval('action_trigger.event_definition_id_seq')
2595                 ,path
2596                 ,collector
2597                 ,label
2598             FROM
2599                 action_trigger.environment
2600             WHERE
2601                 event_def = target_event_def
2602             ;
2603             INSERT INTO action_trigger.event_params (
2604                 event_def
2605                 ,param
2606                 ,value
2607             ) SELECT
2608                 currval('action_trigger.event_definition_id_seq')
2609                 ,param
2610                 ,value
2611             FROM
2612                 action_trigger.event_params
2613             WHERE
2614                 event_def = target_event_def
2615             ;
2616         END LOOP;
2617         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);
2618     END;
2619 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2620
2621 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
2622 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
2623     DECLARE
2624         target_event_def ALIAS FOR $1;
2625         orgs ALIAS FOR $2;
2626         new_interval ALIAS FOR $3;
2627     BEGIN
2628         DROP TABLE IF EXISTS new_atevdefs;
2629         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2630         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
2631             INSERT INTO action_trigger.event_definition (
2632                 active
2633                 ,owner
2634                 ,name
2635                 ,hook
2636                 ,validator
2637                 ,reactor
2638                 ,cleanup_success
2639                 ,cleanup_failure
2640                 ,delay
2641                 ,max_delay
2642                 ,usr_field
2643                 ,opt_in_setting
2644                 ,delay_field
2645                 ,group_field
2646                 ,template
2647                 ,granularity
2648                 ,repeat_delay
2649             ) SELECT
2650                 'f'
2651                 ,orgs[i]
2652                 ,name || ' (clone of '||target_event_def||')'
2653                 ,hook
2654                 ,validator
2655                 ,reactor
2656                 ,cleanup_success
2657                 ,cleanup_failure
2658                 ,new_interval
2659                 ,max_delay
2660                 ,usr_field
2661                 ,opt_in_setting
2662                 ,delay_field
2663                 ,group_field
2664                 ,template
2665                 ,granularity
2666                 ,repeat_delay
2667             FROM
2668                 action_trigger.event_definition
2669             WHERE
2670                 id = target_event_def
2671             ;
2672             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2673             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2674             INSERT INTO action_trigger.environment (
2675                 event_def
2676                 ,path
2677                 ,collector
2678                 ,label
2679             ) SELECT
2680                 currval('action_trigger.event_definition_id_seq')
2681                 ,path
2682                 ,collector
2683                 ,label
2684             FROM
2685                 action_trigger.environment
2686             WHERE
2687                 event_def = target_event_def
2688             ;
2689             INSERT INTO action_trigger.event_params (
2690                 event_def
2691                 ,param
2692                 ,value
2693             ) SELECT
2694                 currval('action_trigger.event_definition_id_seq')
2695                 ,param
2696                 ,value
2697             FROM
2698                 action_trigger.event_params
2699             WHERE
2700                 event_def = target_event_def
2701             ;
2702         END LOOP;
2703         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);
2704     END;
2705 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2706
2707 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
2708 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
2709     DECLARE
2710         org ALIAS FOR $1;
2711         target_event_defs ALIAS FOR $2;
2712     BEGIN
2713         DROP TABLE IF EXISTS new_atevdefs;
2714         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
2715         FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
2716             INSERT INTO action_trigger.event_definition (
2717                 active
2718                 ,owner
2719                 ,name
2720                 ,hook
2721                 ,validator
2722                 ,reactor
2723                 ,cleanup_success
2724                 ,cleanup_failure
2725                 ,delay
2726                 ,max_delay
2727                 ,usr_field
2728                 ,opt_in_setting
2729                 ,delay_field
2730                 ,group_field
2731                 ,template
2732                 ,granularity
2733                 ,repeat_delay
2734             ) SELECT
2735                 'f'
2736                 ,org
2737                 ,name || ' (clone of '||target_event_defs[i]||')'
2738                 ,hook
2739                 ,validator
2740                 ,reactor
2741                 ,cleanup_success
2742                 ,cleanup_failure
2743                 ,delay
2744                 ,max_delay
2745                 ,usr_field
2746                 ,opt_in_setting
2747                 ,delay_field
2748                 ,group_field
2749                 ,template
2750                 ,granularity
2751                 ,repeat_delay
2752             FROM
2753                 action_trigger.event_definition
2754             WHERE
2755                 id = target_event_defs[i]
2756             ;
2757             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
2758             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
2759             INSERT INTO action_trigger.environment (
2760                 event_def
2761                 ,path
2762                 ,collector
2763                 ,label
2764             ) SELECT
2765                 currval('action_trigger.event_definition_id_seq')
2766                 ,path
2767                 ,collector
2768                 ,label
2769             FROM
2770                 action_trigger.environment
2771             WHERE
2772                 event_def = target_event_defs[i]
2773             ;
2774             INSERT INTO action_trigger.event_params (
2775                 event_def
2776                 ,param
2777                 ,value
2778             ) SELECT
2779                 currval('action_trigger.event_definition_id_seq')
2780                 ,param
2781                 ,value
2782             FROM
2783                 action_trigger.event_params
2784             WHERE
2785                 event_def = target_event_defs[i]
2786             ;
2787         END LOOP;
2788         RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
2789     END;
2790 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2791
2792 CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$
2793     UPDATE
2794         action_trigger.event
2795     SET
2796          start_time = NULL
2797         ,update_time = NULL
2798         ,complete_time = NULL
2799         ,update_process = NULL
2800         ,state = 'pending'
2801         ,template_output = NULL
2802         ,error_output = NULL
2803         ,async_output = NULL
2804     WHERE
2805         id = $1;
2806 $$ LANGUAGE SQL;
2807
2808 CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
2809     my ($marcxml) = @_;
2810
2811     use MARC::Record;
2812     use MARC::File::XML;
2813     use MARC::Field;
2814
2815     my $field;
2816     eval {
2817         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2818         $field = $marc->leader();
2819     };
2820     return $field;
2821 $$ LANGUAGE PLPERLU STABLE;
2822
2823 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
2824     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2825
2826     use MARC::Record;
2827     use MARC::File::XML;
2828     use MARC::Field;
2829
2830     my $field;
2831     eval {
2832         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2833         $field = $marc->field($tag);
2834     };
2835     return $field->as_string($subfield,$delimiter);
2836 $$ LANGUAGE PLPERLU STABLE;
2837
2838 CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
2839     my ($marcxml, $tag, $subfield, $delimiter) = @_;
2840
2841     use MARC::Record;
2842     use MARC::File::XML;
2843     use MARC::Field;
2844
2845     my @fields;
2846     eval {
2847         my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2848         @fields = $marc->field($tag);
2849     };
2850     my @texts;
2851     foreach my $field (@fields) {
2852         push @texts, $field->as_string($subfield,$delimiter);
2853     }
2854     return \@texts;
2855 $$ LANGUAGE PLPERLU STABLE;
2856
2857 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
2858     SELECT action.find_hold_matrix_matchpoint(
2859         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2860         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2861         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2862         (SELECT usr FROM action.hold_request WHERE id = $1),
2863         (SELECT requestor FROM action.hold_request WHERE id = $1)
2864     );
2865 $$ LANGUAGE SQL;
2866
2867 CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
2868     SELECT action.hold_request_permit_test(
2869         (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
2870         (SELECT request_lib FROM action.hold_request WHERE id = $1),
2871         (SELECT current_copy FROM action.hold_request WHERE id = $1),
2872         (SELECT usr FROM action.hold_request WHERE id = $1),
2873         (SELECT requestor FROM action.hold_request WHERE id = $1)
2874     );
2875 $$ LANGUAGE SQL;
2876
2877 CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
2878     SELECT action.find_circ_matrix_matchpoint(
2879         (SELECT circ_lib FROM action.circulation WHERE id = $1),
2880         (SELECT target_copy FROM action.circulation WHERE id = $1),
2881         (SELECT usr FROM action.circulation WHERE id = $1),
2882         (SELECT COALESCE(
2883                 NULLIF(phone_renewal,false),
2884                 NULLIF(desk_renewal,false),
2885                 NULLIF(opac_renewal,false),
2886                 false
2887             ) FROM action.circulation WHERE id = $1
2888         )
2889     );
2890 $$ LANGUAGE SQL;
2891
2892 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
2893     DECLARE
2894         test ALIAS FOR $1;
2895     BEGIN
2896         IF NOT test THEN
2897             RAISE EXCEPTION 'assertion';
2898         END IF;
2899     END;
2900 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2901
2902 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
2903     DECLARE
2904         test ALIAS FOR $1;
2905         msg ALIAS FOR $2;
2906     BEGIN
2907         IF NOT test THEN
2908             RAISE EXCEPTION '%', msg;
2909         END IF;
2910     END;
2911 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2912
2913 CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
2914     DECLARE
2915         test ALIAS FOR $1;
2916         fail_msg ALIAS FOR $2;
2917         success_msg ALIAS FOR $3;
2918     BEGIN
2919         IF NOT test THEN
2920             RAISE EXCEPTION '%', fail_msg;
2921         END IF;
2922         RETURN success_msg;
2923     END;
2924 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2925
2926 -- push bib sequence and return starting value for reserved range
2927 CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
2928     DECLARE
2929         bib_count ALIAS FOR $1;
2930         output BIGINT;
2931     BEGIN
2932         PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
2933         FOR output IN
2934             SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
2935         LOOP
2936             RETURN output;
2937         END LOOP;
2938     END;
2939 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2940
2941 -- set a new salted password
2942
2943 CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
2944     DECLARE
2945         usr_id              ALIAS FOR $1;
2946         plain_passwd        ALIAS FOR $2;
2947         plain_salt          TEXT;
2948         md5_passwd          TEXT;
2949     BEGIN
2950
2951         SELECT actor.create_salt('main') INTO plain_salt;
2952
2953         SELECT MD5(plain_passwd) INTO md5_passwd;
2954         
2955         PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
2956
2957         RETURN TRUE;
2958
2959     END;
2960 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
2961
2962
2963 -- convenience functions for handling copy_location maps
2964
2965 CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
2966     DECLARE
2967         table_schema ALIAS FOR $1;
2968         table_name ALIAS FOR $2;
2969         org_shortname ALIAS FOR $3;
2970         org_range ALIAS FOR $4;
2971         proceed BOOLEAN;
2972         org INTEGER;
2973         org_list INTEGER[];
2974         o INTEGER;
2975     BEGIN
2976         EXECUTE 'SELECT EXISTS (
2977             SELECT 1
2978             FROM information_schema.columns
2979             WHERE table_schema = $1
2980             AND table_name = $2
2981             and column_name = ''desired_shelf''
2982         )' INTO proceed USING table_schema, table_name;
2983         IF NOT proceed THEN
2984             RAISE EXCEPTION 'Missing column desired_shelf'; 
2985         END IF;
2986
2987         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
2988         IF org IS NULL THEN
2989             RAISE EXCEPTION 'Cannot find org by shortname';
2990         END IF;
2991
2992         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
2993
2994         EXECUTE 'ALTER TABLE '
2995             || quote_ident(table_name)
2996             || ' DROP COLUMN IF EXISTS x_shelf';
2997         EXECUTE 'ALTER TABLE '
2998             || quote_ident(table_name)
2999             || ' ADD COLUMN x_shelf INTEGER';
3000
3001         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3002             || ' SET x_shelf = id FROM asset_copy_location b'
3003             || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3004             || ' AND b.owning_lib = $1'
3005             || ' AND NOT b.deleted'
3006         USING org;
3007
3008         FOREACH o IN ARRAY org_list LOOP
3009             EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3010                 || ' SET x_shelf = id FROM asset.copy_location b'
3011                 || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
3012                 || ' AND b.owning_lib = $1 AND x_shelf IS NULL'
3013                 || ' AND NOT b.deleted'
3014             USING o;
3015         END LOOP;
3016
3017         EXECUTE 'SELECT migration_tools.assert(
3018             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
3019             ''Cannot find a desired location'',
3020             ''Found all desired locations''
3021         );';
3022
3023     END;
3024 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3025
3026 -- convenience functions for handling circmod maps
3027
3028 CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VOID AS $$
3029     DECLARE
3030         table_schema ALIAS FOR $1;
3031         table_name ALIAS FOR $2;
3032         proceed BOOLEAN;
3033     BEGIN
3034         EXECUTE 'SELECT EXISTS (
3035             SELECT 1
3036             FROM information_schema.columns
3037             WHERE table_schema = $1
3038             AND table_name = $2
3039             and column_name = ''desired_circmod''
3040         )' INTO proceed USING table_schema, table_name;
3041         IF NOT proceed THEN
3042             RAISE EXCEPTION 'Missing column desired_circmod'; 
3043         END IF;
3044
3045         EXECUTE 'ALTER TABLE '
3046             || quote_ident(table_name)
3047             || ' DROP COLUMN IF EXISTS x_circmod';
3048         EXECUTE 'ALTER TABLE '
3049             || quote_ident(table_name)
3050             || ' ADD COLUMN x_circmod TEXT';
3051
3052         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3053             || ' SET x_circmod = code FROM config.circ_modifier b'
3054             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.code))';
3055
3056         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3057             || ' SET x_circmod = code FROM config.circ_modifier b'
3058             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.name))'
3059             || ' AND x_circmod IS NULL';
3060
3061         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3062             || ' SET x_circmod = code FROM config.circ_modifier b'
3063             || ' WHERE BTRIM(UPPER(a.desired_circmod)) = BTRIM(UPPER(b.description))'
3064             || ' AND x_circmod IS NULL';
3065
3066         EXECUTE 'SELECT migration_tools.assert(
3067             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_circmod <> '''' AND x_circmod IS NULL),
3068             ''Cannot find a desired circulation modifier'',
3069             ''Found all desired circulation modifiers''
3070         );';
3071
3072     END;
3073 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3074
3075 -- convenience functions for handling item status maps
3076
3077 CREATE OR REPLACE FUNCTION migration_tools.handle_status (TEXT,TEXT) RETURNS VOID AS $$
3078     DECLARE
3079         table_schema ALIAS FOR $1;
3080         table_name ALIAS FOR $2;
3081         proceed BOOLEAN;
3082     BEGIN
3083         EXECUTE 'SELECT EXISTS (
3084             SELECT 1
3085             FROM information_schema.columns
3086             WHERE table_schema = $1
3087             AND table_name = $2
3088             and column_name = ''desired_status''
3089         )' INTO proceed USING table_schema, table_name;
3090         IF NOT proceed THEN
3091             RAISE EXCEPTION 'Missing column desired_status'; 
3092         END IF;
3093
3094         EXECUTE 'ALTER TABLE '
3095             || quote_ident(table_name)
3096             || ' DROP COLUMN IF EXISTS x_status';
3097         EXECUTE 'ALTER TABLE '
3098             || quote_ident(table_name)
3099             || ' ADD COLUMN x_status INTEGER';
3100
3101         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3102             || ' SET x_status = id FROM config.copy_status b'
3103             || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))';
3104
3105         EXECUTE 'SELECT migration_tools.assert(
3106             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL),
3107             ''Cannot find a desired copy status'',
3108             ''Found all desired copy statuses''
3109         );';
3110
3111     END;
3112 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3113
3114 -- convenience functions for handling org maps
3115
3116 CREATE OR REPLACE FUNCTION migration_tools.handle_org (TEXT,TEXT) RETURNS VOID AS $$
3117     DECLARE
3118         table_schema ALIAS FOR $1;
3119         table_name ALIAS FOR $2;
3120         proceed BOOLEAN;
3121     BEGIN
3122         EXECUTE 'SELECT EXISTS (
3123             SELECT 1
3124             FROM information_schema.columns
3125             WHERE table_schema = $1
3126             AND table_name = $2
3127             and column_name = ''desired_org''
3128         )' INTO proceed USING table_schema, table_name;
3129         IF NOT proceed THEN
3130             RAISE EXCEPTION 'Missing column desired_org'; 
3131         END IF;
3132
3133         EXECUTE 'ALTER TABLE '
3134             || quote_ident(table_name)
3135             || ' DROP COLUMN IF EXISTS x_org';
3136         EXECUTE 'ALTER TABLE '
3137             || quote_ident(table_name)
3138             || ' ADD COLUMN x_org INTEGER';
3139
3140         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3141             || ' SET x_org = id FROM actor.org_unit b'
3142             || ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
3143
3144         EXECUTE 'SELECT migration_tools.assert(
3145             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_org <> '''' AND x_org IS NULL),
3146             ''Cannot find a desired org unit'',
3147             ''Found all desired org units''
3148         );';
3149
3150     END;
3151 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3152
3153 -- convenience function for handling desired_not_migrate
3154
3155 CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$
3156     DECLARE
3157         table_schema ALIAS FOR $1;
3158         table_name ALIAS FOR $2;
3159         proceed BOOLEAN;
3160     BEGIN
3161         EXECUTE 'SELECT EXISTS (
3162             SELECT 1
3163             FROM information_schema.columns
3164             WHERE table_schema = $1
3165             AND table_name = $2
3166             and column_name = ''desired_not_migrate''
3167         )' INTO proceed USING table_schema, table_name;
3168         IF NOT proceed THEN
3169             RAISE EXCEPTION 'Missing column desired_not_migrate'; 
3170         END IF;
3171
3172         EXECUTE 'ALTER TABLE '
3173             || quote_ident(table_name)
3174             || ' DROP COLUMN IF EXISTS x_migrate';
3175         EXECUTE 'ALTER TABLE '
3176             || quote_ident(table_name)
3177             || ' ADD COLUMN x_migrate BOOLEAN';
3178
3179         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3180             || ' SET x_migrate = CASE'
3181             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
3182             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
3183             || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
3184             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
3185             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
3186             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
3187             || ' END';
3188
3189         EXECUTE 'SELECT migration_tools.assert(
3190             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_migrate IS NULL),
3191             ''Not all desired_not_migrate values understood'',
3192             ''All desired_not_migrate values understood''
3193         );';
3194
3195     END;
3196 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3197
3198 -- convenience function for handling desired_not_migrate
3199
3200 CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
3201     DECLARE
3202         table_schema ALIAS FOR $1;
3203         table_name ALIAS FOR $2;
3204         proceed BOOLEAN;
3205     BEGIN
3206         EXECUTE 'SELECT EXISTS (
3207             SELECT 1
3208             FROM information_schema.columns
3209             WHERE table_schema = $1
3210             AND table_name = $2
3211             and column_name = ''desired_barred_or_blocked''
3212         )' INTO proceed USING table_schema, table_name;
3213         IF NOT proceed THEN
3214             RAISE EXCEPTION 'Missing column desired_barred_or_blocked'; 
3215         END IF;
3216
3217         EXECUTE 'ALTER TABLE '
3218             || quote_ident(table_name)
3219             || ' DROP COLUMN IF EXISTS x_barred';
3220         EXECUTE 'ALTER TABLE '
3221             || quote_ident(table_name)
3222             || ' ADD COLUMN x_barred BOOLEAN';
3223
3224         EXECUTE 'ALTER TABLE '
3225             || quote_ident(table_name)
3226             || ' DROP COLUMN IF EXISTS x_blocked';
3227         EXECUTE 'ALTER TABLE '
3228             || quote_ident(table_name)
3229             || ' ADD COLUMN x_blocked BOOLEAN';
3230
3231         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3232             || ' SET x_barred = CASE'
3233             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
3234             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
3235             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3236             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3237             || ' END';
3238
3239         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3240             || ' SET x_blocked = CASE'
3241             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
3242             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
3243             || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
3244             || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
3245             || ' END';
3246
3247         EXECUTE 'SELECT migration_tools.assert(
3248             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
3249             ''Not all desired_barred_or_blocked values understood'',
3250             ''All desired_barred_or_blocked values understood''
3251         );';
3252
3253     END;
3254 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3255
3256 -- convenience function for handling desired_profile
3257
3258 CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
3259     DECLARE
3260         table_schema ALIAS FOR $1;
3261         table_name ALIAS FOR $2;
3262         proceed BOOLEAN;
3263     BEGIN
3264         EXECUTE 'SELECT EXISTS (
3265             SELECT 1
3266             FROM information_schema.columns
3267             WHERE table_schema = $1
3268             AND table_name = $2
3269             and column_name = ''desired_profile''
3270         )' INTO proceed USING table_schema, table_name;
3271         IF NOT proceed THEN
3272             RAISE EXCEPTION 'Missing column desired_profile'; 
3273         END IF;
3274
3275         EXECUTE 'ALTER TABLE '
3276             || quote_ident(table_name)
3277             || ' DROP COLUMN IF EXISTS x_profile';
3278         EXECUTE 'ALTER TABLE '
3279             || quote_ident(table_name)
3280             || ' ADD COLUMN x_profile INTEGER';
3281
3282         EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
3283             || ' SET x_profile = b.id FROM permission.grp_tree b'
3284             || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
3285
3286         EXECUTE 'SELECT migration_tools.assert(
3287             NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL),
3288             ''Cannot find a desired profile'',
3289             ''Found all desired profiles''
3290         );';
3291
3292     END;
3293 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3294
3295 -- convenience function for handling desired actor stat cats
3296
3297 CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3298     DECLARE
3299         table_schema ALIAS FOR $1;
3300         table_name ALIAS FOR $2;
3301         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3302         org_shortname ALIAS FOR $4;
3303         proceed BOOLEAN;
3304         org INTEGER;
3305         org_list INTEGER[];
3306         sc TEXT;
3307         sce TEXT;
3308     BEGIN
3309
3310         SELECT 'desired_sc' || field_suffix INTO sc;
3311         SELECT 'desired_sce' || field_suffix INTO sce;
3312
3313         EXECUTE 'SELECT EXISTS (
3314             SELECT 1
3315             FROM information_schema.columns
3316             WHERE table_schema = $1
3317             AND table_name = $2
3318             and column_name = $3
3319         )' INTO proceed USING table_schema, table_name, sc;
3320         IF NOT proceed THEN
3321             RAISE EXCEPTION 'Missing column %', sc; 
3322         END IF;
3323         EXECUTE 'SELECT EXISTS (
3324             SELECT 1
3325             FROM information_schema.columns
3326             WHERE table_schema = $1
3327             AND table_name = $2
3328             and column_name = $3
3329         )' INTO proceed USING table_schema, table_name, sce;
3330         IF NOT proceed THEN
3331             RAISE EXCEPTION 'Missing column %', sce; 
3332         END IF;
3333
3334         SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
3335         IF org IS NULL THEN
3336             RAISE EXCEPTION 'Cannot find org by shortname';
3337         END IF;
3338         SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
3339
3340         -- caller responsible for their own truncates though we try to prevent duplicates
3341         EXECUTE 'INSERT INTO actor_stat_cat (owner, name)
3342             SELECT DISTINCT
3343                  $1
3344                 ,BTRIM('||sc||')
3345             FROM 
3346                 ' || quote_ident(table_name) || '
3347             WHERE
3348                 NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3349                 AND NOT EXISTS (
3350                     SELECT id
3351                     FROM actor.stat_cat
3352                     WHERE owner = ANY ($2)
3353                     AND name = BTRIM('||sc||')
3354                 )
3355                 AND NOT EXISTS (
3356                     SELECT id
3357                     FROM actor_stat_cat
3358                     WHERE owner = ANY ($2)
3359                     AND name = BTRIM('||sc||')
3360                 )
3361             ORDER BY 2;'
3362         USING org, org_list;
3363
3364         EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value)
3365             SELECT DISTINCT
3366                 COALESCE(
3367                     (SELECT id
3368                         FROM actor.stat_cat
3369                         WHERE owner = ANY ($2)
3370                         AND BTRIM('||sc||') = BTRIM(name))
3371                    ,(SELECT id
3372                         FROM actor_stat_cat
3373                         WHERE owner = ANY ($2)
3374                         AND BTRIM('||sc||') = BTRIM(name))
3375                 )
3376                 ,$1
3377                 ,BTRIM('||sce||')
3378             FROM 
3379                 ' || quote_ident(table_name) || '
3380             WHERE
3381                     NULLIF(BTRIM('||sc||'),'''') IS NOT NULL
3382                 AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL
3383                 AND NOT EXISTS (
3384                     SELECT id
3385                     FROM actor.stat_cat_entry
3386                     WHERE stat_cat = (
3387                         SELECT id
3388                         FROM actor.stat_cat
3389                         WHERE owner = ANY ($2)
3390                         AND BTRIM('||sc||') = BTRIM(name)
3391                     ) AND value = BTRIM('||sce||')
3392                     AND owner = ANY ($2)
3393                 )
3394                 AND NOT EXISTS (
3395                     SELECT id
3396                     FROM actor_stat_cat_entry
3397                     WHERE stat_cat = (
3398                         SELECT id
3399                         FROM actor_stat_cat
3400                         WHERE owner = ANY ($2)
3401                         AND BTRIM('||sc||') = BTRIM(name)
3402                     ) AND value = BTRIM('||sce||')
3403                     AND owner = ANY ($2)
3404                 )
3405             ORDER BY 1,3;'
3406         USING org, org_list;
3407     END;
3408 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3409
3410 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
3411     DECLARE
3412         table_schema ALIAS FOR $1;
3413         table_name ALIAS FOR $2;
3414         field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
3415         org_shortname ALIAS FOR $4;
3416         proceed BOOLEAN;
3417         org INTEGER;
3418         org_list INTEGER[];
3419         o INTEGER;
3420         sc TEXT;
3421         sce TEXT;
3422     BEGIN
3423         SELECT 'desired_sc' || field_suffix INTO sc;
3424         SELECT 'desired_sce' || field_suffix INTO sce;
3425         EXECUTE 'SELECT EXISTS (
3426             SELECT 1
3427             FROM information_schema.columns
3428             WHERE table_schema = $1
3429             AND table_name = $2
3430             and column_name = $3
3431         )' INTO proceed USING table_schema, table_name, sc;
3432         IF NOT proceed THEN
3433             RAISE EXCEPTION 'Missing column %', sc; 
3434         END IF;
3435         EXECUTE 'SELECT EXISTS (
3436             SELECT 1
3437             FROM information_schema.columns
3438             WHERE table_schema = $1
3439             AND table_name = $2
3440             and column_name = $3
3441         )' INTO proceed U