add x_org logic to handle_shelf
[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 -- FIXME: there's probably a more efficient way to implement this
652 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
653     DECLARE
654         street1 TEXT := $1;
655                 _r RECORD;
656     BEGIN
657                 DROP TABLE IF EXISTS mig_usps_suffixes;
658                 CREATE TEMP TABLE mig_usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
659                 INSERT INTO mig_usps_suffixes VALUES
660                         ('ALLEE','ALY'),
661                         ('ALLEY','ALY'),
662                         ('ALLY','ALY'),
663                         ('ALY','ALY'),
664                         ('ANEX','ANX'),
665                         ('ANNEX','ANX'),
666                         ('ANNX','ANX'),
667                         ('ANX','ANX'),
668                         ('ARCADE','ARC'),
669                         ('ARC','ARC'),
670                         ('AV','AVE'),
671                         ('AVE','AVE'),
672                         ('AVEN','AVE'),
673                         ('AVENU','AVE'),
674                         ('AVENUE','AVE'),
675                         ('AVN','AVE'),
676                         ('AVNUE','AVE'),
677                         ('BAYOO','BYU'),
678                         ('BAYOU','BYU'),
679                         ('BCH','BCH'),
680                         ('BEACH','BCH'),
681                         ('BEND','BND'),
682                         ('BLF','BLF'),
683                         ('BLUF','BLF'),
684                         ('BLUFF','BLF'),
685                         ('BLUFFS','BLFS'),
686                         ('BLVD','BLVD'),
687                         ('BND','BND'),
688                         ('BOT','BTM'),
689                         ('BOTTM','BTM'),
690                         ('BOTTOM','BTM'),
691                         ('BOUL','BLVD'),
692                         ('BOULEVARD','BLVD'),
693                         ('BOULV','BLVD'),
694                         ('BRANCH','BR'),
695                         ('BR','BR'),
696                         ('BRDGE','BRG'),
697                         ('BRG','BRG'),
698                         ('BRIDGE','BRG'),
699                         ('BRK','BRK'),
700                         ('BRNCH','BR'),
701                         ('BROOK','BRK'),
702                         ('BROOKS','BRKS'),
703                         ('BTM','BTM'),
704                         ('BURG','BG'),
705                         ('BURGS','BGS'),
706                         ('BYPA','BYP'),
707                         ('BYPAS','BYP'),
708                         ('BYPASS','BYP'),
709                         ('BYP','BYP'),
710                         ('BYPS','BYP'),
711                         ('CAMP','CP'),
712                         ('CANYN','CYN'),
713                         ('CANYON','CYN'),
714                         ('CAPE','CPE'),
715                         ('CAUSEWAY','CSWY'),
716                         ('CAUSWAY','CSWY'),
717                         ('CEN','CTR'),
718                         ('CENT','CTR'),
719                         ('CENTER','CTR'),
720                         ('CENTERS','CTRS'),
721                         ('CENTR','CTR'),
722                         ('CENTRE','CTR'),
723                         ('CIRC','CIR'),
724                         ('CIR','CIR'),
725                         ('CIRCL','CIR'),
726                         ('CIRCLE','CIR'),
727                         ('CIRCLES','CIRS'),
728                         ('CK','CRK'),
729                         ('CLB','CLB'),
730                         ('CLF','CLF'),
731                         ('CLFS','CLFS'),
732                         ('CLIFF','CLF'),
733                         ('CLIFFS','CLFS'),
734                         ('CLUB','CLB'),
735                         ('CMP','CP'),
736                         ('CNTER','CTR'),
737                         ('CNTR','CTR'),
738                         ('CNYN','CYN'),
739                         ('COMMON','CMN'),
740                         ('COR','COR'),
741                         ('CORNER','COR'),
742                         ('CORNERS','CORS'),
743                         ('CORS','CORS'),
744                         ('COURSE','CRSE'),
745                         ('COURT','CT'),
746                         ('COURTS','CTS'),
747                         ('COVE','CV'),
748                         ('COVES','CVS'),
749                         ('CP','CP'),
750                         ('CPE','CPE'),
751                         ('CRCL','CIR'),
752                         ('CRCLE','CIR'),
753                         ('CR','CRK'),
754                         ('CRECENT','CRES'),
755                         ('CREEK','CRK'),
756                         ('CRESCENT','CRES'),
757                         ('CRES','CRES'),
758                         ('CRESENT','CRES'),
759                         ('CREST','CRST'),
760                         ('CRK','CRK'),
761                         ('CROSSING','XING'),
762                         ('CROSSROAD','XRD'),
763                         ('CRSCNT','CRES'),
764                         ('CRSE','CRSE'),
765                         ('CRSENT','CRES'),
766                         ('CRSNT','CRES'),
767                         ('CRSSING','XING'),
768                         ('CRSSNG','XING'),
769                         ('CRT','CT'),
770                         ('CSWY','CSWY'),
771                         ('CT','CT'),
772                         ('CTR','CTR'),
773                         ('CTS','CTS'),
774                         ('CURVE','CURV'),
775                         ('CV','CV'),
776                         ('CYN','CYN'),
777                         ('DALE','DL'),
778                         ('DAM','DM'),
779                         ('DIV','DV'),
780                         ('DIVIDE','DV'),
781                         ('DL','DL'),
782                         ('DM','DM'),
783                         ('DR','DR'),
784                         ('DRIV','DR'),
785                         ('DRIVE','DR'),
786                         ('DRIVES','DRS'),
787                         ('DRV','DR'),
788                         ('DVD','DV'),
789                         ('DV','DV'),
790                         ('ESTATE','EST'),
791                         ('ESTATES','ESTS'),
792                         ('EST','EST'),
793                         ('ESTS','ESTS'),
794                         ('EXP','EXPY'),
795                         ('EXPRESS','EXPY'),
796                         ('EXPRESSWAY','EXPY'),
797                         ('EXPR','EXPY'),
798                         ('EXPW','EXPY'),
799                         ('EXPY','EXPY'),
800                         ('EXTENSION','EXT'),
801                         ('EXTENSIONS','EXTS'),
802                         ('EXT','EXT'),
803                         ('EXTN','EXT'),
804                         ('EXTNSN','EXT'),
805                         ('EXTS','EXTS'),
806                         ('FALL','FALL'),
807                         ('FALLS','FLS'),
808                         ('FERRY','FRY'),
809                         ('FIELD','FLD'),
810                         ('FIELDS','FLDS'),
811                         ('FLAT','FLT'),
812                         ('FLATS','FLTS'),
813                         ('FLD','FLD'),
814                         ('FLDS','FLDS'),
815                         ('FLS','FLS'),
816                         ('FLT','FLT'),
817                         ('FLTS','FLTS'),
818                         ('FORD','FRD'),
819                         ('FORDS','FRDS'),
820                         ('FOREST','FRST'),
821                         ('FORESTS','FRST'),
822                         ('FORGE','FRG'),
823                         ('FORGES','FRGS'),
824                         ('FORG','FRG'),
825                         ('FORK','FRK'),
826                         ('FORKS','FRKS'),
827                         ('FORT','FT'),
828                         ('FRD','FRD'),
829                         ('FREEWAY','FWY'),
830                         ('FREEWY','FWY'),
831                         ('FRG','FRG'),
832                         ('FRK','FRK'),
833                         ('FRKS','FRKS'),
834                         ('FRRY','FRY'),
835                         ('FRST','FRST'),
836                         ('FRT','FT'),
837                         ('FRWAY','FWY'),
838                         ('FRWY','FWY'),
839                         ('FRY','FRY'),
840                         ('FT','FT'),
841                         ('FWY','FWY'),
842                         ('GARDEN','GDN'),
843                         ('GARDENS','GDNS'),
844                         ('GARDN','GDN'),
845                         ('GATEWAY','GTWY'),
846                         ('GATEWY','GTWY'),
847                         ('GATWAY','GTWY'),
848                         ('GDN','GDN'),
849                         ('GDNS','GDNS'),
850                         ('GLEN','GLN'),
851                         ('GLENS','GLNS'),
852                         ('GLN','GLN'),
853                         ('GRDEN','GDN'),
854                         ('GRDN','GDN'),
855                         ('GRDNS','GDNS'),
856                         ('GREEN','GRN'),
857                         ('GREENS','GRNS'),
858                         ('GRN','GRN'),
859                         ('GROVE','GRV'),
860                         ('GROVES','GRVS'),
861                         ('GROV','GRV'),
862                         ('GRV','GRV'),
863                         ('GTWAY','GTWY'),
864                         ('GTWY','GTWY'),
865                         ('HARB','HBR'),
866                         ('HARBOR','HBR'),
867                         ('HARBORS','HBRS'),
868                         ('HARBR','HBR'),
869                         ('HAVEN','HVN'),
870                         ('HAVN','HVN'),
871                         ('HBR','HBR'),
872                         ('HEIGHT','HTS'),
873                         ('HEIGHTS','HTS'),
874                         ('HGTS','HTS'),
875                         ('HIGHWAY','HWY'),
876                         ('HIGHWY','HWY'),
877                         ('HILL','HL'),
878                         ('HILLS','HLS'),
879                         ('HIWAY','HWY'),
880                         ('HIWY','HWY'),
881                         ('HL','HL'),
882                         ('HLLW','HOLW'),
883                         ('HLS','HLS'),
884                         ('HOLLOW','HOLW'),
885                         ('HOLLOWS','HOLW'),
886                         ('HOLW','HOLW'),
887                         ('HOLWS','HOLW'),
888                         ('HRBOR','HBR'),
889                         ('HT','HTS'),
890                         ('HTS','HTS'),
891                         ('HVN','HVN'),
892                         ('HWAY','HWY'),
893                         ('HWY','HWY'),
894                         ('INLET','INLT'),
895                         ('INLT','INLT'),
896                         ('IS','IS'),
897                         ('ISLAND','IS'),
898                         ('ISLANDS','ISS'),
899                         ('ISLANDS','SLNDS'),
900                         ('ISLANDS','SS'),
901                         ('ISLE','ISLE'),
902                         ('ISLES','ISLE'),
903                         ('ISLND','IS'),
904                         ('I','SLNDS'),
905                         ('ISS','ISS'),
906                         ('JCTION','JCT'),
907                         ('JCT','JCT'),
908                         ('JCTN','JCT'),
909                         ('JCTNS','JCTS'),
910                         ('JCTS','JCTS'),
911                         ('JUNCTION','JCT'),
912                         ('JUNCTIONS','JCTS'),
913                         ('JUNCTN','JCT'),
914                         ('JUNCTON','JCT'),
915                         ('KEY','KY'),
916                         ('KEYS','KYS'),
917                         ('KNL','KNL'),
918                         ('KNLS','KNLS'),
919                         ('KNOL','KNL'),
920                         ('KNOLL','KNL'),
921                         ('KNOLLS','KNLS'),
922                         ('KY','KY'),
923                         ('KYS','KYS'),
924                         ('LAKE','LK'),
925                         ('LAKES','LKS'),
926                         ('LA','LN'),
927                         ('LANDING','LNDG'),
928                         ('LAND','LAND'),
929                         ('LANE','LN'),
930                         ('LANES','LN'),
931                         ('LCK','LCK'),
932                         ('LCKS','LCKS'),
933                         ('LDGE','LDG'),
934                         ('LDG','LDG'),
935                         ('LF','LF'),
936                         ('LGT','LGT'),
937                         ('LIGHT','LGT'),
938                         ('LIGHTS','LGTS'),
939                         ('LK','LK'),
940                         ('LKS','LKS'),
941                         ('LNDG','LNDG'),
942                         ('LNDNG','LNDG'),
943                         ('LN','LN'),
944                         ('LOAF','LF'),
945                         ('LOCK','LCK'),
946                         ('LOCKS','LCKS'),
947                         ('LODGE','LDG'),
948                         ('LODG','LDG'),
949                         ('LOOP','LOOP'),
950                         ('LOOPS','LOOP'),
951                         ('MALL','MALL'),
952                         ('MANOR','MNR'),
953                         ('MANORS','MNRS'),
954                         ('MDW','MDW'),
955                         ('MDWS','MDWS'),
956                         ('MEADOW','MDW'),
957                         ('MEADOWS','MDWS'),
958                         ('MEDOWS','MDWS'),
959                         ('MEWS','MEWS'),
960                         ('MILL','ML'),
961                         ('MILLS','MLS'),
962                         ('MISSION','MSN'),
963                         ('MISSN','MSN'),
964                         ('ML','ML'),
965                         ('MLS','MLS'),
966                         ('MNR','MNR'),
967                         ('MNRS','MNRS'),
968                         ('MNTAIN','MTN'),
969                         ('MNT','MT'),
970                         ('MNTN','MTN'),
971                         ('MNTNS','MTNS'),
972                         ('MOTORWAY','MTWY'),
973                         ('MOUNTAIN','MTN'),
974                         ('MOUNTAINS','MTNS'),
975                         ('MOUNTIN','MTN'),
976                         ('MOUNT','MT'),
977                         ('MSN','MSN'),
978                         ('MSSN','MSN'),
979                         ('MTIN','MTN'),
980                         ('MT','MT'),
981                         ('MTN','MTN'),
982                         ('NCK','NCK'),
983                         ('NECK','NCK'),
984                         ('ORCHARD','ORCH'),
985                         ('ORCH','ORCH'),
986                         ('ORCHRD','ORCH'),
987                         ('OVAL','OVAL'),
988                         ('OVERPASS','OPAS'),
989                         ('OVL','OVAL'),
990                         ('PARK','PARK'),
991                         ('PARKS','PARK'),
992                         ('PARKWAY','PKWY'),
993                         ('PARKWAYS','PKWY'),
994                         ('PARKWY','PKWY'),
995                         ('PASSAGE','PSGE'),
996                         ('PASS','PASS'),
997                         ('PATH','PATH'),
998                         ('PATHS','PATH'),
999                         ('PIKE','PIKE'),
1000                         ('PIKES','PIKE'),
1001                         ('PINE','PNE'),
1002                         ('PINES','PNES'),
1003                         ('PK','PARK'),
1004                         ('PKWAY','PKWY'),
1005                         ('PKWY','PKWY'),
1006                         ('PKWYS','PKWY'),
1007                         ('PKY','PKWY'),
1008                         ('PLACE','PL'),
1009                         ('PLAINES','PLNS'),
1010                         ('PLAIN','PLN'),
1011                         ('PLAINS','PLNS'),
1012                         ('PLAZA','PLZ'),
1013                         ('PLN','PLN'),
1014                         ('PLNS','PLNS'),
1015                         ('PL','PL'),
1016                         ('PLZA','PLZ'),
1017                         ('PLZ','PLZ'),
1018                         ('PNES','PNES'),
1019                         ('POINT','PT'),
1020                         ('POINTS','PTS'),
1021                         ('PORT','PRT'),
1022                         ('PORTS','PRTS'),
1023                         ('PRAIRIE','PR'),
1024                         ('PRARIE','PR'),
1025                         ('PRK','PARK'),
1026                         ('PR','PR'),
1027                         ('PRR','PR'),
1028                         ('PRT','PRT'),
1029                         ('PRTS','PRTS'),
1030                         ('PT','PT'),
1031                         ('PTS','PTS'),
1032                         ('RADIAL','RADL'),
1033                         ('RADIEL','RADL'),
1034                         ('RADL','RADL'),
1035                         ('RAD','RADL'),
1036                         ('RAMP','RAMP'),
1037                         ('RANCHES','RNCH'),
1038                         ('RANCH','RNCH'),
1039                         ('RAPID','RPD'),
1040                         ('RAPIDS','RPDS'),
1041                         ('RDGE','RDG'),
1042                         ('RDG','RDG'),
1043                         ('RDGS','RDGS'),
1044                         ('RD','RD'),
1045                         ('RDS','RDS'),
1046                         ('REST','RST'),
1047                         ('RIDGE','RDG'),
1048                         ('RIDGES','RDGS'),
1049                         ('RIVER','RIV'),
1050                         ('RIV','RIV'),
1051                         ('RIVR','RIV'),
1052                         ('RNCH','RNCH'),
1053                         ('RNCHS','RNCH'),
1054                         ('ROAD','RD'),
1055                         ('ROADS','RDS'),
1056                         ('ROUTE','RTE'),
1057                         ('ROW','ROW'),
1058                         ('RPD','RPD'),
1059                         ('RPDS','RPDS'),
1060                         ('RST','RST'),
1061                         ('RUE','RUE'),
1062                         ('RUN','RUN'),
1063                         ('RVR','RIV'),
1064                         ('SHL','SHL'),
1065                         ('SHLS','SHLS'),
1066                         ('SHOAL','SHL'),
1067                         ('SHOALS','SHLS'),
1068                         ('SHOAR','SHR'),
1069                         ('SHOARS','SHRS'),
1070                         ('SHORE','SHR'),
1071                         ('SHORES','SHRS'),
1072                         ('SHR','SHR'),
1073                         ('SHRS','SHRS'),
1074                         ('SKYWAY','SKWY'),
1075                         ('SMT','SMT'),
1076                         ('SPG','SPG'),
1077                         ('SPGS','SPGS'),
1078                         ('SPNG','SPG'),
1079                         ('SPNGS','SPGS'),
1080                         ('SPRING','SPG'),
1081                         ('SPRINGS','SPGS'),
1082                         ('SPRNG','SPG'),
1083                         ('SPRNGS','SPGS'),
1084                         ('SPUR','SPUR'),
1085                         ('SPURS','SPUR'),
1086                         ('SQRE','SQ'),
1087                         ('SQR','SQ'),
1088                         ('SQRS','SQS'),
1089                         ('SQ','SQ'),
1090                         ('SQUARE','SQ'),
1091                         ('SQUARES','SQS'),
1092                         ('SQU','SQ'),
1093                         ('STA','STA'),
1094                         ('STATION','STA'),
1095                         ('STATN','STA'),
1096                         ('STN','STA'),
1097                         ('STRA','STRA'),
1098                         ('STRAVEN','STRA'),
1099                         ('STRAVENUE','STRA'),
1100                         ('STRAVE','STRA'),
1101                         ('STRAVN','STRA'),
1102                         ('STRAV','STRA'),
1103                         ('STREAM','STRM'),
1104                         ('STREETS','STS'),
1105                         ('STREET','ST'),
1106                         ('STREME','STRM'),
1107                         ('STRM','STRM'),
1108                         ('STR','ST'),
1109                         ('STRT','ST'),
1110                         ('STRVN','STRA'),
1111                         ('STRVNUE','STRA'),
1112                         ('ST','ST'),
1113                         ('SUMIT','SMT'),
1114                         ('SUMITT','SMT'),
1115                         ('SUMMIT','SMT'),
1116                         ('TERRACE','TER'),
1117                         ('TERR','TER'),
1118                         ('TER','TER'),
1119                         ('THROUGHWAY','TRWY'),
1120                         ('TPKE','TPKE'),
1121                         ('TPK','TPKE'),
1122                         ('TRACES','TRCE'),
1123                         ('TRACE','TRCE'),
1124                         ('TRACKS','TRAK'),
1125                         ('TRACK','TRAK'),
1126                         ('TRAFFICWAY','TRFY'),
1127                         ('TRAILS','TRL'),
1128                         ('TRAIL','TRL'),
1129                         ('TRAK','TRAK'),
1130                         ('TRCE','TRCE'),
1131                         ('TRFY','TRFY'),
1132                         ('TRKS','TRAK'),
1133                         ('TRK','TRAK'),
1134                         ('TRLS','TRL'),
1135                         ('TRL','TRL'),
1136                         ('TRNPK','TPKE'),
1137                         ('TRPK','TPKE'),
1138                         ('TR','TRL'),
1139                         ('TUNEL','TUNL'),
1140                         ('TUNLS','TUNL'),
1141                         ('TUNL','TUNL'),
1142                         ('TUNNELS','TUNL'),
1143                         ('TUNNEL','TUNL'),
1144                         ('TUNNL','TUNL'),
1145                         ('TURNPIKE','TPKE'),
1146                         ('TURNPK','TPKE'),
1147                         ('UNDERPASS','UPAS'),
1148                         ('UNIONS','UNS'),
1149                         ('UNION','UN'),
1150                         ('UN','UN'),
1151                         ('VALLEYS','VLYS'),
1152                         ('VALLEY','VLY'),
1153                         ('VALLY','VLY'),
1154                         ('VDCT','IA'),
1155                         ('VIADCT','VIA'),
1156                         ('VIADUCT','IA'),
1157                         ('VIADUCT','VIA'),
1158                         ('VIA','VIA'),
1159                         ('VIEWS','VWS'),
1160                         ('VIEW','VW'),
1161                         ('VILLAGES','VLGS'),
1162                         ('VILLAGE','VLG'),
1163                         ('VILLAG','VLG'),
1164                         ('VILLE','VL'),
1165                         ('VILLG','VLG'),
1166                         ('VILLIAGE','VLG'),
1167                         ('VILL','VLG'),
1168                         ('VISTA','VIS'),
1169                         ('VIST','VIS'),
1170                         ('VIS','VIS'),
1171                         ('VLGS','VLGS'),
1172                         ('VLG','VLG'),
1173                         ('VLLY','VLY'),
1174                         ('VL','VL'),
1175                         ('VLYS','VLYS'),
1176                         ('VLY','VLY'),
1177                         ('VSTA','VIS'),
1178                         ('VST','VIS'),
1179                         ('VWS','VWS'),
1180                         ('VW','VW'),
1181                         ('WALKS','WALK'),
1182                         ('WALK','WALK'),
1183                         ('WALL','WALL'),
1184                         ('WAYS','WAYS'),
1185                         ('WAY','WAY'),
1186                         ('WELLS','WLS'),
1187                         ('WELL','WL'),
1188                         ('WLS','WLS'),
1189                         ('WY','WAY'),
1190                         ('XING','XING');
1191                 FOR _r IN (SELECT * FROM mig_usps_suffixes) LOOP
1192                         street1 := REGEXP_REPLACE( street1, ' ' || _r.suffix_from || '$', ' ' || _r.suffix_to, 'i');
1193                 END LOOP;
1194                 RETURN street1;
1195     END;
1196 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1197
1198 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
1199     DECLARE
1200         n TEXT := o;
1201     BEGIN
1202         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
1203             IF o::BIGINT < t THEN
1204                 n = o::BIGINT + t;
1205             END IF;
1206         END IF;
1207
1208         RETURN n;
1209     END;
1210 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
1211
1212 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
1213     DECLARE
1214         migration_schema ALIAS FOR $1;
1215         output TEXT;
1216     BEGIN
1217         FOR output IN
1218             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
1219         LOOP
1220             RETURN output;
1221         END LOOP;
1222     END;
1223 $$ LANGUAGE PLPGSQL STRICT STABLE;
1224
1225 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
1226     DECLARE
1227         migration_schema ALIAS FOR $1;
1228         output TEXT;
1229     BEGIN
1230         FOR output IN
1231             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
1232         LOOP
1233             RETURN output;
1234         END LOOP;
1235     END;
1236 $$ LANGUAGE PLPGSQL STRICT STABLE;
1237
1238 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
1239     DECLARE
1240         migration_schema ALIAS FOR $1;
1241         output TEXT;
1242     BEGIN
1243         FOR output IN
1244             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
1245         LOOP
1246             RETURN output;
1247         END LOOP;
1248     END;
1249 $$ LANGUAGE PLPGSQL STRICT STABLE;
1250
1251 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
1252     DECLARE
1253         migration_schema ALIAS FOR $1;
1254         output TEXT;
1255     BEGIN
1256         FOR output IN
1257             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
1258         LOOP
1259             RETURN output;
1260         END LOOP;
1261     END;
1262 $$ LANGUAGE PLPGSQL STRICT STABLE;
1263
1264 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
1265     DECLARE
1266         migration_schema ALIAS FOR $1;
1267         profile_map TEXT;
1268         patron_table ALIAS FOR $2;
1269         default_patron_profile ALIAS FOR $3;
1270         sql TEXT;
1271         sql_update TEXT;
1272         sql_where1 TEXT := '';
1273         sql_where2 TEXT := '';
1274         sql_where3 TEXT := '';
1275         output RECORD;
1276     BEGIN
1277         SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
1278         FOR output IN 
1279             EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
1280         LOOP
1281             sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
1282             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);
1283             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);
1284             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);
1285             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,'') || ';';
1286             --RAISE INFO 'sql = %', sql;
1287             PERFORM migration_tools.exec( $1, sql );
1288         END LOOP;
1289         PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;'  );
1290         BEGIN
1291             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
1292         EXCEPTION
1293             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
1294         END;
1295     END;
1296 $$ LANGUAGE PLPGSQL STRICT STABLE;
1297
1298 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
1299     DECLARE
1300         migration_schema ALIAS FOR $1;
1301         field_map TEXT;
1302         item_table ALIAS FOR $2;
1303         sql TEXT;
1304         sql_update TEXT;
1305         sql_where1 TEXT := '';
1306         sql_where2 TEXT := '';
1307         sql_where3 TEXT := '';
1308         output RECORD;
1309     BEGIN
1310         SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
1311         FOR output IN 
1312             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1313         LOOP
1314             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 ';
1315             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);
1316             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);
1317             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);
1318             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,'') || ';';
1319             --RAISE INFO 'sql = %', sql;
1320             PERFORM migration_tools.exec( $1, sql );
1321         END LOOP;
1322         BEGIN
1323             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
1324         EXCEPTION
1325             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
1326         END;
1327     END;
1328 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1329
1330 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
1331     DECLARE
1332         migration_schema ALIAS FOR $1;
1333         base_copy_location_map TEXT;
1334         item_table ALIAS FOR $2;
1335         sql TEXT;
1336         sql_update TEXT;
1337         sql_where1 TEXT := '';
1338         sql_where2 TEXT := '';
1339         sql_where3 TEXT := '';
1340         output RECORD;
1341     BEGIN
1342         SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
1343         FOR output IN 
1344             EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
1345         LOOP
1346             sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
1347             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);
1348             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);
1349             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);
1350             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,'') || ';';
1351             --RAISE INFO 'sql = %', sql;
1352             PERFORM migration_tools.exec( $1, sql );
1353         END LOOP;
1354         BEGIN
1355             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
1356         EXCEPTION
1357             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
1358         END;
1359     END;
1360 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1361
1362 -- 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
1363 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
1364     DECLARE
1365         migration_schema ALIAS FOR $1;
1366         field_map TEXT;
1367         circ_table ALIAS FOR $2;
1368         item_table ALIAS FOR $3;
1369         patron_table ALIAS FOR $4;
1370         sql TEXT;
1371         sql_update TEXT;
1372         sql_where1 TEXT := '';
1373         sql_where2 TEXT := '';
1374         sql_where3 TEXT := '';
1375         sql_where4 TEXT := '';
1376         output RECORD;
1377     BEGIN
1378         SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
1379         FOR output IN 
1380             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
1381         LOOP
1382             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 ';
1383             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);
1384             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);
1385             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);
1386             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);
1387             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,'') || ';';
1388             --RAISE INFO 'sql = %', sql;
1389             PERFORM migration_tools.exec( $1, sql );
1390         END LOOP;
1391         BEGIN
1392             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
1393         EXCEPTION
1394             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
1395         END;
1396     END;
1397 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1398
1399 -- expand_barcode
1400 --   $barcode      source barcode
1401 --   $prefix       prefix to add to barcode, NULL = add no prefix
1402 --   $maxlen       maximum length of barcode; default to 14 if left NULL
1403 --   $pad          padding string to apply to left of source barcode before adding
1404 --                 prefix and suffix; set to NULL or '' if no padding is desired
1405 --   $suffix       suffix to add to barcode, NULL = add no suffix
1406 --
1407 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
1408 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
1409 --
1410 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
1411     my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
1412
1413     # default case
1414     return unless defined $barcode;
1415
1416     $prefix     = '' unless defined $prefix;
1417     $maxlen ||= 14;
1418     $pad        = '0' unless defined $pad;
1419     $suffix     = '' unless defined $suffix;
1420
1421     # bail out if adding prefix and suffix would bring new barcode over max length
1422     return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
1423
1424     my $new_barcode = $barcode;
1425     if ($pad ne '') {
1426         my $pad_length = $maxlen - length($prefix) - length($suffix);
1427         if (length($barcode) < $pad_length) {
1428             # assuming we always want padding on the left
1429             # also assuming that it is possible to have the pad string be longer than 1 character
1430             $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
1431         }
1432     }
1433
1434     # bail out if adding prefix and suffix would bring new barcode over max length
1435     return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
1436
1437     return "$prefix$new_barcode$suffix";
1438 $$ LANGUAGE PLPERLU STABLE;
1439
1440 -- remove previous version of this function
1441 DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
1442
1443 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
1444     DECLARE
1445         attempt_value ALIAS FOR $1;
1446         datatype ALIAS FOR $2;
1447     BEGIN
1448         EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
1449         RETURN attempt_value;
1450     EXCEPTION
1451         WHEN OTHERS THEN RETURN NULL;
1452     END;
1453 $$ LANGUAGE PLPGSQL STRICT STABLE;
1454
1455 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
1456     DECLARE
1457         attempt_value ALIAS FOR $1;
1458         fail_value ALIAS FOR $2;
1459         output DATE;
1460     BEGIN
1461         FOR output IN
1462             EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
1463         LOOP
1464             RETURN output;
1465         END LOOP;
1466     EXCEPTION
1467         WHEN OTHERS THEN
1468             FOR output IN
1469                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1470             LOOP
1471                 RETURN output;
1472             END LOOP;
1473     END;
1474 $$ LANGUAGE PLPGSQL STRICT STABLE;
1475
1476 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
1477     DECLARE
1478         attempt_value ALIAS FOR $1;
1479         fail_value ALIAS FOR $2;
1480         output TIMESTAMPTZ;
1481     BEGIN
1482         FOR output IN
1483             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
1484         LOOP
1485             RETURN output;
1486         END LOOP;
1487     EXCEPTION
1488         WHEN OTHERS THEN
1489             FOR output IN
1490                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
1491             LOOP
1492                 RETURN output;
1493             END LOOP;
1494     END;
1495 $$ LANGUAGE PLPGSQL STRICT STABLE;
1496
1497 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
1498     DECLARE
1499         attempt_value ALIAS FOR $1;
1500         fail_value ALIAS FOR $2;
1501         output DATE;
1502     BEGIN
1503         FOR output IN
1504             EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
1505         LOOP
1506             RETURN output;
1507         END LOOP;
1508     EXCEPTION
1509         WHEN OTHERS THEN
1510             FOR output IN
1511                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
1512             LOOP
1513                 RETURN output;
1514             END LOOP;
1515     END;
1516 $$ LANGUAGE PLPGSQL STRICT STABLE;
1517
1518 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
1519     DECLARE
1520         attempt_value ALIAS FOR $1;
1521         fail_value ALIAS FOR $2;
1522         output TIMESTAMP;
1523     BEGIN
1524             output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
1525             RETURN output;
1526     EXCEPTION
1527         WHEN OTHERS THEN
1528             FOR output IN
1529                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
1530             LOOP
1531                 RETURN output;
1532             END LOOP;
1533     END;
1534 $$ LANGUAGE PLPGSQL STRICT STABLE;
1535
1536 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1537     DECLARE
1538         attempt_value ALIAS FOR $1;
1539         fail_value ALIAS FOR $2;
1540         output NUMERIC(8,2);
1541     BEGIN
1542         FOR output IN
1543             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
1544         LOOP
1545             RETURN output;
1546         END LOOP;
1547     EXCEPTION
1548         WHEN OTHERS THEN
1549             FOR output IN
1550                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1551             LOOP
1552                 RETURN output;
1553             END LOOP;
1554     END;
1555 $$ LANGUAGE PLPGSQL STRICT STABLE;
1556
1557 CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1558     DECLARE
1559         attempt_value ALIAS FOR $1;
1560         fail_value ALIAS FOR $2;
1561         output NUMERIC(6,2);
1562     BEGIN
1563         FOR output IN
1564             EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
1565         LOOP
1566             RETURN output;
1567         END LOOP;
1568     EXCEPTION
1569         WHEN OTHERS THEN
1570             FOR output IN
1571                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1572             LOOP
1573                 RETURN output;
1574             END LOOP;
1575     END;
1576 $$ LANGUAGE PLPGSQL STRICT STABLE;
1577
1578 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
1579     DECLARE
1580         attempt_value ALIAS FOR $1;
1581         fail_value ALIAS FOR $2;
1582         output NUMERIC(8,2);
1583     BEGIN
1584         IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
1585             RAISE EXCEPTION 'too many digits';
1586         END IF;
1587         FOR output IN
1588             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;'
1589         LOOP
1590             RETURN output;
1591         END LOOP;
1592     EXCEPTION
1593         WHEN OTHERS THEN
1594             FOR output IN
1595                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
1596             LOOP
1597                 RETURN output;
1598             END LOOP;
1599     END;
1600 $$ LANGUAGE PLPGSQL STRICT STABLE;
1601
1602 CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
1603     DECLARE
1604         attempt_value ALIAS FOR $1;
1605         fail_value ALIAS FOR $2;
1606         output NUMERIC(6,2);
1607     BEGIN
1608         IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
1609             RAISE EXCEPTION 'too many digits';
1610         END IF;
1611         FOR output IN
1612             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;'
1613         LOOP
1614             RETURN output;
1615         END LOOP;
1616     EXCEPTION
1617         WHEN OTHERS THEN
1618             FOR output IN
1619                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
1620             LOOP
1621                 RETURN output;
1622             END LOOP;
1623     END;
1624 $$ LANGUAGE PLPGSQL STRICT STABLE;
1625
1626 -- add_codabar_checkdigit
1627 --   $barcode      source barcode
1628 --
1629 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
1630 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1631 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
1632 -- input string does not meet those requirements, it is returned unchanged.
1633 --
1634 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
1635     my $barcode = shift;
1636
1637     return $barcode if $barcode !~ /^\d{13,14}$/;
1638     $barcode = substr($barcode, 0, 13); # ignore 14th digit
1639     my @digits = split //, $barcode;
1640     my $total = 0;
1641     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
1642     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
1643     my $remainder = $total % 10;
1644     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
1645     return $barcode . $checkdigit; 
1646 $$ LANGUAGE PLPERLU STRICT STABLE;
1647
1648 -- add_code39mod43_checkdigit
1649 --   $barcode      source barcode
1650 --
1651 -- If the source string is 13 or 14 characters long and contains only valid
1652 -- Code 39 mod 43 characters, adds or replaces the 14th
1653 -- character with a checkdigit computed according to the usual algorithm for library barcodes
1654 -- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>.  If the
1655 -- input string does not meet those requirements, it is returned unchanged.
1656 --
1657 CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
1658     my $barcode = shift;
1659
1660     return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
1661     $barcode = substr($barcode, 0, 13); # ignore 14th character
1662
1663     my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
1664     my %nums = map { $valid_chars[$_] => $_ } (0..42);
1665
1666     my $total = 0;
1667     $total += $nums{$_} foreach split(//, $barcode);
1668     my $remainder = $total % 43;
1669     my $checkdigit = $valid_chars[$remainder];
1670     return $barcode . $checkdigit;
1671 $$ LANGUAGE PLPERLU STRICT STABLE;
1672
1673 -- add_mod16_checkdigit
1674 --   $barcode      source barcode
1675 --
1676 -- https://www.activebarcode.com/codes/checkdigit/modulo16.html
1677
1678 CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
1679     my $barcode = shift;
1680
1681     my @digits = split //, $barcode;
1682     my $total = 0;
1683     foreach $digit (@digits) {
1684         if ($digit =~ /[0-9]/) { $total += $digit;
1685         } elsif ($digit eq '-') { $total += 10;
1686         } elsif ($digit eq '$') { $total += 11;
1687         } elsif ($digit eq ':') { $total += 12;
1688         } elsif ($digit eq '/') { $total += 13;
1689         } elsif ($digit eq '.') { $total += 14;
1690         } elsif ($digit eq '+') { $total += 15;
1691         } elsif ($digit eq 'A') { $total += 16;
1692         } elsif ($digit eq 'B') { $total += 17;
1693         } elsif ($digit eq 'C') { $total += 18;
1694         } elsif ($digit eq 'D') { $total += 19;
1695         } else { die "invalid digit <$digit>";
1696         }
1697     }
1698     my $remainder = $total % 16;
1699     my $difference = 16 - $remainder;
1700     my $checkdigit;
1701     if ($difference < 10) { $checkdigit = $difference;
1702     } elsif ($difference == 10) { $checkdigit = '-';
1703     } elsif ($difference == 11) { $checkdigit = '$';
1704     } elsif ($difference == 12) { $checkdigit = ':';
1705     } elsif ($difference == 13) { $checkdigit = '/';
1706     } elsif ($difference == 14) { $checkdigit = '.';
1707     } elsif ($difference == 15) { $checkdigit = '+';
1708     } else { die "error calculating checkdigit";
1709     }
1710
1711     return $barcode . $checkdigit;
1712 $$ LANGUAGE PLPERLU STRICT STABLE;
1713
1714 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
1715   DECLARE
1716     phone TEXT := $1;
1717     areacode TEXT := $2;
1718     temp TEXT := '';
1719     output TEXT := '';
1720     n_digits INTEGER := 0;
1721   BEGIN
1722     temp := phone;
1723     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
1724     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
1725     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
1726     IF n_digits = 7 AND areacode <> '' THEN
1727       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
1728       output := (areacode || '-' || temp);
1729     ELSE
1730       output := temp;
1731     END IF;
1732     RETURN output;
1733   END;
1734
1735 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
1736
1737 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
1738   my ($marcxml, $pos, $value) = @_;
1739
1740   use MARC::Record;
1741   use MARC::File::XML;
1742
1743   my $xml = $marcxml;
1744   eval {
1745     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1746     my $leader = $marc->leader();
1747     substr($leader, $pos, 1) = $value;
1748     $marc->leader($leader);
1749     $xml = $marc->as_xml_record;
1750     $xml =~ s/^<\?.+?\?>$//mo;
1751     $xml =~ s/\n//sgo;
1752     $xml =~ s/>\s+</></sgo;
1753   };
1754   return $xml;
1755 $$ LANGUAGE PLPERLU STABLE;
1756
1757 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
1758   my ($marcxml, $pos, $value) = @_;
1759
1760   use MARC::Record;
1761   use MARC::File::XML;
1762
1763   my $xml = $marcxml;
1764   eval {
1765     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1766     my $f008 = $marc->field('008');
1767
1768     if ($f008) {
1769        my $field = $f008->data();
1770        substr($field, $pos, 1) = $value;
1771        $f008->update($field);
1772        $xml = $marc->as_xml_record;
1773        $xml =~ s/^<\?.+?\?>$//mo;
1774        $xml =~ s/\n//sgo;
1775        $xml =~ s/>\s+</></sgo;
1776     }
1777   };
1778   return $xml;
1779 $$ LANGUAGE PLPERLU STABLE;
1780
1781
1782 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
1783   DECLARE
1784     profile ALIAS FOR $1;
1785   BEGIN
1786     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
1787   END;
1788 $$ LANGUAGE PLPGSQL STRICT STABLE;
1789
1790
1791 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
1792   BEGIN
1793     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
1794   END;
1795 $$ LANGUAGE PLPGSQL STRICT STABLE;
1796
1797
1798 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
1799
1800   my ($marcxml, $tags) = @_;
1801
1802   use MARC::Record;
1803   use MARC::File::XML;
1804
1805   my $xml = $marcxml;
1806
1807   eval {
1808     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1809     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
1810
1811     my @incumbents = ();
1812
1813     foreach my $field ( $marc->fields() ) {
1814       push @incumbents, $field->as_formatted();
1815     }
1816
1817     foreach $field ( $to_insert->fields() ) {
1818       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
1819         $marc->insert_fields_ordered( ($field) );
1820       }
1821     }
1822
1823     $xml = $marc->as_xml_record;
1824     $xml =~ s/^<\?.+?\?>$//mo;
1825     $xml =~ s/\n//sgo;
1826     $xml =~ s/>\s+</></sgo;
1827   };
1828
1829   return $xml;
1830
1831 $$ LANGUAGE PLPERLU STABLE;
1832
1833 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
1834
1835 -- Usage:
1836 --
1837 --   First make sure the circ matrix is loaded and the circulations
1838 --   have been staged to the extent possible (but at the very least
1839 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1840 --   circ modifiers must also be in place.
1841 --
1842 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1843 --
1844
1845 DECLARE
1846   circ_lib             INT;
1847   target_copy          INT;
1848   usr                  INT;
1849   is_renewal           BOOLEAN;
1850   this_duration_rule   INT;
1851   this_fine_rule       INT;
1852   this_max_fine_rule   INT;
1853   rcd                  config.rule_circ_duration%ROWTYPE;
1854   rrf                  config.rule_recurring_fine%ROWTYPE;
1855   rmf                  config.rule_max_fine%ROWTYPE;
1856   circ                 INT;
1857   n                    INT := 0;
1858   n_circs              INT;
1859   
1860 BEGIN
1861
1862   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1863
1864   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1865
1866     -- Fetch the correct rules for this circulation
1867     EXECUTE ('
1868       SELECT
1869         circ_lib,
1870         target_copy,
1871         usr,
1872         CASE
1873           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1874           ELSE FALSE
1875         END
1876       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1877       INTO circ_lib, target_copy, usr, is_renewal ;
1878     SELECT
1879       INTO this_duration_rule,
1880            this_fine_rule,
1881            this_max_fine_rule
1882       duration_rule,
1883       recurring_fine_rule,
1884       max_fine_rule
1885       FROM action.item_user_circ_test(
1886         circ_lib,
1887         target_copy,
1888         usr,
1889         is_renewal
1890         );
1891     SELECT INTO rcd * FROM config.rule_circ_duration
1892       WHERE id = this_duration_rule;
1893     SELECT INTO rrf * FROM config.rule_recurring_fine
1894       WHERE id = this_fine_rule;
1895     SELECT INTO rmf * FROM config.rule_max_fine
1896       WHERE id = this_max_fine_rule;
1897
1898     -- Apply the rules to this circulation
1899     EXECUTE ('UPDATE ' || tablename || ' c
1900     SET
1901       duration_rule = rcd.name,
1902       recurring_fine_rule = rrf.name,
1903       max_fine_rule = rmf.name,
1904       duration = rcd.normal,
1905       recurring_fine = rrf.normal,
1906       max_fine =
1907         CASE rmf.is_percent
1908           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
1909           ELSE rmf.amount
1910         END,
1911       renewal_remaining = rcd.max_renewals
1912     FROM
1913       config.rule_circ_duration rcd,
1914       config.rule_recurring_fine rrf,
1915       config.rule_max_fine rmf,
1916                         asset.copy ac
1917     WHERE
1918       rcd.id = ' || this_duration_rule || ' AND
1919       rrf.id = ' || this_fine_rule || ' AND
1920       rmf.id = ' || this_max_fine_rule || ' AND
1921                         ac.id = c.target_copy AND
1922       c.id = ' || circ || ';');
1923
1924     -- Keep track of where we are in the process
1925     n := n + 1;
1926     IF (n % 100 = 0) THEN
1927       RAISE INFO '%', n || ' of ' || n_circs
1928         || ' (' || (100*n/n_circs) || '%) circs updated.';
1929     END IF;
1930
1931   END LOOP;
1932
1933   RETURN;
1934 END;
1935
1936 $$ LANGUAGE plpgsql;
1937
1938 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
1939
1940 -- Usage:
1941 --
1942 --   First make sure the circ matrix is loaded and the circulations
1943 --   have been staged to the extent possible (but at the very least
1944 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
1945 --   circ modifiers must also be in place.
1946 --
1947 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
1948 --
1949
1950 DECLARE
1951   circ_lib             INT;
1952   target_copy          INT;
1953   usr                  INT;
1954   is_renewal           BOOLEAN;
1955   this_duration_rule   INT;
1956   this_fine_rule       INT;
1957   this_max_fine_rule   INT;
1958   rcd                  config.rule_circ_duration%ROWTYPE;
1959   rrf                  config.rule_recurring_fine%ROWTYPE;
1960   rmf                  config.rule_max_fine%ROWTYPE;
1961   circ                 INT;
1962   n                    INT := 0;
1963   n_circs              INT;
1964   
1965 BEGIN
1966
1967   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
1968
1969   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
1970
1971     -- Fetch the correct rules for this circulation
1972     EXECUTE ('
1973       SELECT
1974         circ_lib,
1975         target_copy,
1976         usr,
1977         CASE
1978           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
1979           ELSE FALSE
1980         END
1981       FROM ' || tablename || ' WHERE id = ' || circ || ';')
1982       INTO circ_lib, target_copy, usr, is_renewal ;
1983     SELECT
1984       INTO this_duration_rule,
1985            this_fine_rule,
1986            this_max_fine_rule
1987       duration_rule,
1988       recuring_fine_rule,
1989       max_fine_rule
1990       FROM action.find_circ_matrix_matchpoint(
1991         circ_lib,
1992         target_copy,
1993         usr,
1994         is_renewal
1995         );
1996     SELECT INTO rcd * FROM config.rule_circ_duration
1997       WHERE id = this_duration_rule;
1998     SELECT INTO rrf * FROM config.rule_recurring_fine
1999       WHERE id = this_fine_rule;
2000     SELECT INTO rmf * FROM config.rule_max_fine
2001       WHERE id = this_max_fine_rule;
2002
2003     -- Apply the rules to this circulation
2004     EXECUTE ('UPDATE ' || tablename || ' c
2005     SET
2006       duration_rule = rcd.name,
2007       recuring_fine_rule = rrf.name,
2008       max_fine_rule = rmf.name,
2009       duration = rcd.normal,
2010       recuring_fine = rrf.normal,
2011       max_fine =
2012         CASE rmf.is_percent
2013           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2014           ELSE rmf.amount
2015         END,
2016       renewal_remaining = rcd.max_renewals
2017     FROM
2018       config.rule_circ_duration rcd,
2019       config.rule_recuring_fine rrf,
2020       config.rule_max_fine rmf,
2021                         asset.copy ac
2022     WHERE
2023       rcd.id = ' || this_duration_rule || ' AND
2024       rrf.id = ' || this_fine_rule || ' AND
2025       rmf.id = ' || this_max_fine_rule || ' AND
2026                         ac.id = c.target_copy AND
2027       c.id = ' || circ || ';');
2028
2029     -- Keep track of where we are in the process
2030     n := n + 1;
2031     IF (n % 100 = 0) THEN
2032       RAISE INFO '%', n || ' of ' || n_circs
2033         || ' (' || (100*n/n_circs) || '%) circs updated.';
2034     END IF;
2035
2036   END LOOP;
2037
2038   RETURN;
2039 END;
2040
2041 $$ LANGUAGE plpgsql;
2042
2043 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
2044
2045 -- Usage:
2046 --
2047 --   First make sure the circ matrix is loaded and the circulations
2048 --   have been staged to the extent possible (but at the very least
2049 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
2050 --   circ modifiers must also be in place.
2051 --
2052 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
2053 --
2054
2055 DECLARE
2056   circ_lib             INT;
2057   target_copy          INT;
2058   usr                  INT;
2059   is_renewal           BOOLEAN;
2060   this_duration_rule   INT;
2061   this_fine_rule       INT;
2062   this_max_fine_rule   INT;
2063   rcd                  config.rule_circ_duration%ROWTYPE;
2064   rrf                  config.rule_recurring_fine%ROWTYPE;
2065   rmf                  config.rule_max_fine%ROWTYPE;
2066   circ                 INT;
2067   n                    INT := 0;
2068   n_circs              INT;
2069   
2070 BEGIN
2071
2072   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2073
2074   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2075
2076     -- Fetch the correct rules for this circulation
2077     EXECUTE ('
2078       SELECT
2079         circ_lib,
2080         target_copy,
2081         usr,
2082         CASE
2083           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2084           ELSE FALSE
2085         END
2086       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2087       INTO circ_lib, target_copy, usr, is_renewal ;
2088     SELECT
2089       INTO this_duration_rule,
2090            this_fine_rule,
2091            this_max_fine_rule
2092       (matchpoint).duration_rule,
2093       (matchpoint).recurring_fine_rule,
2094       (matchpoint).max_fine_rule
2095       FROM action.find_circ_matrix_matchpoint(
2096         circ_lib,
2097         target_copy,
2098         usr,
2099         is_renewal
2100         );
2101     SELECT INTO rcd * FROM config.rule_circ_duration
2102       WHERE id = this_duration_rule;
2103     SELECT INTO rrf * FROM config.rule_recurring_fine
2104       WHERE id = this_fine_rule;
2105     SELECT INTO rmf * FROM config.rule_max_fine
2106       WHERE id = this_max_fine_rule;
2107
2108     -- Apply the rules to this circulation
2109     EXECUTE ('UPDATE ' || tablename || ' c
2110     SET
2111       duration_rule = rcd.name,
2112       recurring_fine_rule = rrf.name,
2113       max_fine_rule = rmf.name,
2114       duration = rcd.normal,
2115       recurring_fine = rrf.normal,
2116       max_fine =
2117         CASE rmf.is_percent
2118           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
2119           ELSE rmf.amount
2120         END,
2121       renewal_remaining = rcd.max_renewals,
2122       grace_period = rrf.grace_period
2123     FROM
2124       config.rule_circ_duration rcd,
2125       config.rule_recurring_fine rrf,
2126       config.rule_max_fine rmf,
2127                         asset.copy ac
2128     WHERE
2129       rcd.id = ' || this_duration_rule || ' AND
2130       rrf.id = ' || this_fine_rule || ' AND
2131       rmf.id = ' || this_max_fine_rule || ' AND
2132                         ac.id = c.target_copy AND
2133       c.id = ' || circ || ';');
2134
2135     -- Keep track of where we are in the process
2136     n := n + 1;
2137     IF (n % 100 = 0) THEN
2138       RAISE INFO '%', n || ' of ' || n_circs
2139         || ' (' || (100*n/n_circs) || '%) circs updated.';
2140     END IF;
2141
2142   END LOOP;
2143
2144   RETURN;
2145 END;
2146
2147 $$ LANGUAGE plpgsql;
2148
2149 -- TODO: make another version of the procedure below that can work with specified copy staging tables
2150 -- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
2151 CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
2152 DECLARE
2153     context_lib             INT;
2154     charge_lost_on_zero     BOOLEAN;
2155     min_price               NUMERIC;
2156     max_price               NUMERIC;
2157     default_price           NUMERIC;
2158     working_price           NUMERIC;
2159
2160 BEGIN
2161
2162     SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
2163         FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
2164
2165     SELECT INTO charge_lost_on_zero value
2166         FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
2167
2168     SELECT INTO min_price value
2169         FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
2170
2171     SELECT INTO max_price value
2172         FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
2173
2174     SELECT INTO default_price value
2175         FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
2176
2177     SELECT INTO working_price price FROM asset.copy WHERE id = item;
2178
2179     IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
2180         working_price := default_price;
2181     END IF;
2182
2183     IF (max_price IS NOT NULL AND working_price > max_price) THEN
2184         working_price := max_price;
2185     END IF;
2186
2187     IF (min_price IS NOT NULL AND working_price < min_price) THEN
2188         IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
2189             working_price := min_price;
2190         END IF;
2191     END IF;
2192
2193     RETURN working_price;
2194
2195 END;
2196
2197 $$ LANGUAGE plpgsql;
2198
2199 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
2200
2201 -- Usage:
2202 --
2203 --   First make sure the circ matrix is loaded and the circulations
2204 --   have been staged to the extent possible (but at the very least
2205 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
2206 --   circ modifiers must also be in place.
2207 --
2208 --   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
2209 --
2210
2211 DECLARE
2212   circ_lib             INT;
2213   target_copy          INT;
2214   usr                  INT;
2215   is_renewal           BOOLEAN;
2216   this_duration_rule   INT;
2217   this_fine_rule       INT;
2218   this_max_fine_rule   INT;
2219   rcd                  config.rule_circ_duration%ROWTYPE;
2220   rrf                  config.rule_recurring_fine%ROWTYPE;
2221   rmf                  config.rule_max_fine%ROWTYPE;
2222   n                    INT := 0;
2223   n_circs              INT := 1;
2224   
2225 BEGIN
2226
2227   --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
2228
2229   --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
2230
2231     -- Fetch the correct rules for this circulation
2232     EXECUTE ('
2233       SELECT
2234         circ_lib,
2235         target_copy,
2236         usr,
2237         CASE
2238           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
2239           ELSE FALSE
2240         END
2241       FROM ' || tablename || ' WHERE id = ' || circ || ';')
2242       INTO circ_lib, target_copy, usr, is_renewal ;
2243     SELECT
2244       INTO this_duration_rule,
2245            this_fine_rule,
2246            this_max_fine_rule
2247       (matchpoint).duration_rule,
2248       (matchpoint).recurring_fine_rule,
2249       (matchpoint).max_fine_rule
2250       FROM action.find_circ_matrix_matchpoint(
2251         circ_lib,
2252         target_copy,
2253         usr,
2254         is_renewal
2255         );
2256     SELECT INTO rcd * FROM config.rule_circ_duration
2257       WHERE id = this_duration_rule;
2258     SELECT INTO rrf * FROM config.rule_recurring_fine
2259       WHERE id = this_fine_rule;
2260     SELECT INTO rmf * FROM config.rule_max_fine
2261       WHERE id = this_max_fine_rule;
2262
2263     -- Apply the rules to this circulation
2264     EXECUTE ('UPDATE ' || tablename || ' c
2265     SET
2266       duration_rule = rcd.name,
2267       recurring_fine_rule = rrf.name,
2268       max_fine_rule = rmf.name,
2269       duration = rcd.normal,
2270       recurring_fine = rrf.normal,
2271       max_fine =
2272         CASE rmf.is_percent
2273           WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
2274           ELSE rmf.amount
2275         END,
2276       renewal_remaining = rcd.max_renewals,
2277       grace_period = rrf.grace_period
2278     FROM
2279       config.rule_circ_duration rcd,
2280       config.rule_recurring_fine rrf,
2281       config.rule_max_fine rmf,
2282                         asset.copy ac
2283     WHERE
2284       rcd.id = ' || this_duration_rule || ' AND
2285       rrf.id = ' || this_fine_rule || ' AND
2286       rmf.id = ' || this_max_fine_rule || ' AND
2287                         ac.id = c.target_copy AND
2288       c.id = ' || circ || ';');
2289
2290     -- Keep track of where we are in the process
2291     n := n + 1;
2292     IF (n % 100 = 0) THEN
2293       RAISE INFO '%', n || ' of ' || n_circs
2294         || ' (' || (100*n/n_circs) || '%) circs updated.';
2295     END IF;
2296
2297   --END LOOP;
2298
2299   RETURN;
2300 END;
2301
2302 $$ LANGUAGE plpgsql;
2303
2304
2305
2306
2307 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
2308
2309 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
2310 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
2311
2312 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
2313 -- TODO: Add a similar tool for actor stat cats, which behave differently.
2314
2315 DECLARE
2316         c                    TEXT := schemaname || '.asset_copy_legacy';
2317         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
2318         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
2319         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
2320         stat_cat                                                 INT;
2321   stat_cat_entry       INT;
2322   
2323 BEGIN
2324
2325   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
2326
2327                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
2328
2329                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
2330                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
2331                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
2332
2333   END LOOP;
2334
2335   RETURN;
2336 END;
2337
2338 $$ LANGUAGE plpgsql;
2339
2340 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
2341
2342 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
2343 --        This will assign standing penalties as needed.
2344
2345 DECLARE
2346   org_unit  INT;
2347   usr       INT;
2348
2349 BEGIN
2350
2351   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
2352
2353     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
2354   
2355       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
2356
2357     END LOOP;
2358
2359   END LOOP;
2360
2361   RETURN;
2362
2363 END;
2364
2365 $$ LANGUAGE plpgsql;
2366
2367
2368 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
2369
2370 BEGIN
2371   INSERT INTO metabib.metarecord (fingerprint, master_record)
2372     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2373       FROM  biblio.record_entry b
2374       WHERE NOT b.deleted
2375         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)
2376         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2377       ORDER BY b.fingerprint, b.quality DESC;
2378   INSERT INTO metabib.metarecord_source_map (metarecord, source)
2379     SELECT  m.id, r.id
2380       FROM  biblio.record_entry r
2381       JOIN  metabib.metarecord m USING (fingerprint)
2382      WHERE  NOT r.deleted;
2383 END;
2384   
2385 $$ LANGUAGE plpgsql;
2386
2387
2388 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
2389
2390 BEGIN
2391   INSERT INTO metabib.metarecord (fingerprint, master_record)
2392     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
2393       FROM  biblio.record_entry b
2394       WHERE NOT b.deleted
2395         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)
2396         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
2397       ORDER BY b.fingerprint, b.quality DESC;
2398   INSERT INTO metabib.metarecord_source_map (metarecord, source)
2399     SELECT  m.id, r.id
2400       FROM  biblio.record_entry r
2401         JOIN metabib.metarecord m USING (fingerprint)
2402       WHERE NOT r.deleted
2403         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);
2404 END;
2405     
2406 $$ LANGUAGE plpgsql;
2407
2408
2409 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
2410
2411 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
2412 --        Then SELECT migration_tools.create_cards('m_foo');
2413
2414 DECLARE
2415         u                    TEXT := schemaname || '.actor_usr_legacy';
2416         c                    TEXT := schemaname || '.actor_card';
2417   
2418 BEGIN
2419
2420         EXECUTE ('DELETE FROM ' || c || ';');
2421         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
2422         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
2423
2424   RETURN;
2425
2426 END;
2427
2428 $$ LANGUAGE plpgsql;
2429
2430
2431 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
2432
2433   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2434
2435   my ($marcxml, $shortname) = @_;
2436
2437   use MARC::Record;
2438   use MARC::File::XML;
2439
2440   my $xml = $marcxml;
2441
2442   eval {
2443     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2444
2445     foreach my $field ( $marc->field('856') ) {
2446       if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
2447            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
2448         $field->add_subfields( '9' => $shortname );
2449                                 $field->update( ind2 => '0');
2450       }
2451     }
2452
2453     $xml = $marc->as_xml_record;
2454     $xml =~ s/^<\?.+?\?>$//mo;
2455     $xml =~ s/\n//sgo;
2456     $xml =~ s/>\s+</></sgo;
2457   };
2458
2459   return $xml;
2460
2461 $$ LANGUAGE PLPERLU STABLE;
2462
2463 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
2464
2465   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
2466
2467   my ($marcxml, $shortname) = @_;
2468
2469   use MARC::Record;
2470   use MARC::File::XML;
2471
2472   my $xml = $marcxml;
2473
2474   eval {
2475     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
2476
2477     foreach my $field ( $marc->field('856') ) {
2478       if ( ! $field->as_string('9') ) {
2479         $field->add_subfields( '9' => $shortname );
2480       }
2481     }
2482
2483     $xml = $marc->as_xml_record;
2484     $xml =~ s/^<\?.+?\?>$//mo;
2485     $xml =~ s/\n//sgo;
2486     $xml =~ s/>\s+</></sgo;
2487   };
2488
2489   return $xml;
2490
2491 $$ LANGUAGE PLPERLU STABLE;
2492
2493
2494 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
2495
2496 DECLARE
2497   old_volume   BIGINT;
2498   new_volume   BIGINT;
2499   bib          BIGINT;
2500   owner        INTEGER;
2501   old_label    TEXT;
2502   remainder    BIGINT;
2503
2504 BEGIN
2505
2506   -- Bail out if asked to change the label to ##URI##
2507   IF new_label = '##URI##' THEN
2508     RETURN;
2509   END IF;
2510
2511   -- Gather information
2512   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2513   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2514   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
2515
2516   -- Bail out if the label already is ##URI##
2517   IF old_label = '##URI##' THEN
2518     RETURN;
2519   END IF;
2520
2521   -- Bail out if the call number label is already correct
2522   IF new_volume = old_volume THEN
2523     RETURN;
2524   END IF;
2525
2526   -- Check whether we already have a destination volume available
2527   SELECT id INTO new_volume FROM asset.call_number 
2528     WHERE 
2529       record = bib AND
2530       owning_lib = owner AND
2531       label = new_label AND
2532       NOT deleted;
2533
2534   -- Create destination volume if needed
2535   IF NOT FOUND THEN
2536     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class) 
2537       VALUES (1, 1, bib, owner, new_label, cn_class);
2538     SELECT id INTO new_volume FROM asset.call_number
2539       WHERE 
2540         record = bib AND
2541         owning_lib = owner AND
2542         label = new_label AND
2543         NOT deleted;
2544   END IF;
2545
2546   -- Move copy to destination
2547   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2548
2549   -- Delete source volume if it is now empty
2550   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2551   IF NOT FOUND THEN
2552     DELETE FROM asset.call_number WHERE id = old_volume;
2553   END IF;
2554
2555 END;
2556
2557 $$ LANGUAGE plpgsql;
2558
2559 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
2560
2561         my $input = $_[0];
2562         my %zipdata;
2563
2564         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
2565
2566         while (<FH>) {
2567                 chomp;
2568                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
2569                 $zipdata{$zip} = [$city, $state, $county];
2570         }
2571
2572         if (defined $zipdata{$input}) {
2573                 my ($city, $state, $county) = @{$zipdata{$input}};
2574                 return [$city, $state, $county];
2575         } elsif (defined $zipdata{substr $input, 0, 5}) {
2576                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
2577                 return [$city, $state, $county];
2578         } else {
2579                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
2580         }
2581   
2582 $$ LANGUAGE PLPERLU STABLE;
2583
2584 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
2585
2586 DECLARE
2587   ou  INT;
2588         org_unit_depth INT;
2589         ou_parent INT;
2590         parent_depth INT;
2591   errors_found BOOLEAN;
2592         ou_shortname TEXT;
2593         parent_shortname TEXT;
2594         ou_type_name TEXT;
2595         parent_type TEXT;
2596         type_id INT;
2597         type_depth INT;
2598         type_parent INT;
2599         type_parent_depth INT;
2600         proper_parent TEXT;
2601
2602 BEGIN
2603
2604         errors_found := FALSE;
2605
2606 -- Checking actor.org_unit_type
2607
2608         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
2609
2610                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
2611                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
2612
2613                 IF type_parent IS NOT NULL THEN
2614
2615                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
2616
2617                         IF type_depth - type_parent_depth <> 1 THEN
2618                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
2619                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
2620                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
2621                                         ou_type_name, type_depth, parent_type, type_parent_depth;
2622                                 errors_found := TRUE;
2623
2624                         END IF;
2625
2626                 END IF;
2627
2628         END LOOP;
2629
2630 -- Checking actor.org_unit
2631
2632   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
2633
2634                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
2635                 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;
2636                 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;
2637                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
2638                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
2639                 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;
2640                 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;
2641
2642                 IF ou_parent IS NOT NULL THEN
2643
2644                         IF      (org_unit_depth - parent_depth <> 1) OR (
2645                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
2646                         ) THEN
2647                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).', 
2648                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
2649                                 errors_found := TRUE;
2650                         END IF;
2651
2652                 END IF;
2653
2654   END LOOP;
2655
2656         IF NOT errors_found THEN
2657                 RAISE INFO 'No errors found.';
2658         END IF;
2659
2660   RETURN;
2661
2662 END;
2663
2664 $$ LANGUAGE plpgsql;
2665
2666
2667 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
2668
2669 BEGIN   
2670
2671         DELETE FROM asset.opac_visible_copies;
2672
2673         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
2674                 SELECT DISTINCT
2675                         cp.id, cp.circ_lib, cn.record
2676                 FROM
2677                         asset.copy cp
2678                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
2679                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
2680                         JOIN asset.copy_location cl ON (cp.location = cl.id)
2681                         JOIN config.copy_status cs ON (cp.status = cs.id)
2682                         JOIN biblio.record_entry b ON (cn.record = b.id)
2683                 WHERE 
2684                         NOT cp.deleted AND
2685                         NOT cn.deleted AND
2686                         NOT b.deleted AND
2687                         cs.opac_visible AND
2688                         cl.opac_visible AND
2689                         cp.opac_visible AND
2690                         a.opac_visible AND
2691                         cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
2692
2693 END;
2694
2695 $$ LANGUAGE plpgsql;
2696
2697
2698 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$
2699
2700 DECLARE
2701   old_volume     BIGINT;
2702   new_volume     BIGINT;
2703   bib            BIGINT;
2704   old_owning_lib INTEGER;
2705         old_label      TEXT;
2706   remainder      BIGINT;
2707
2708 BEGIN
2709
2710   -- Gather information
2711   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
2712   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
2713   SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume;
2714
2715         -- Bail out if the new_owning_lib is not the ID of an org_unit
2716         IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN
2717                 RAISE WARNING 
2718                         '% is not a valid actor.org_unit ID; no change made.', 
2719                                 new_owning_lib;
2720                 RETURN;
2721         END IF;
2722
2723   -- Bail out discreetly if the owning_lib is already correct
2724   IF new_owning_lib = old_owning_lib THEN
2725     RETURN;
2726   END IF;
2727
2728   -- Check whether we already have a destination volume available
2729   SELECT id INTO new_volume FROM asset.call_number 
2730     WHERE 
2731       record = bib AND
2732       owning_lib = new_owning_lib AND
2733       label = old_label AND
2734       NOT deleted;
2735
2736   -- Create destination volume if needed
2737   IF NOT FOUND THEN
2738     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) 
2739       VALUES (1, 1, bib, new_owning_lib, old_label);
2740     SELECT id INTO new_volume FROM asset.call_number
2741       WHERE 
2742         record = bib AND
2743         owning_lib = new_owning_lib AND
2744         label = old_label AND
2745         NOT deleted;
2746   END IF;
2747
2748   -- Move copy to destination
2749   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
2750
2751   -- Delete source volume if it is now empty
2752   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
2753   IF NOT FOUND THEN
2754     DELETE FROM asset.call_number WHERE id = old_volume;
2755   END IF;
2756
2757 END;
2758
2759 $$ LANGUAGE plpgsql;
2760
2761
2762 CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$
2763
2764 -- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER).
2765
2766 DECLARE
2767         new_owning_lib  INTEGER;
2768
2769 BEGIN
2770
2771         -- Parse the new_owner as an org unit ID or shortname
2772         IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN
2773                 SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner;
2774                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2775         ELSIF new_owner ~ E'^[0-9]+$' THEN
2776                 IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN
2777                         RAISE INFO 
2778                                 '%',
2779                                 E'You don\'t need to put the actor.org_unit ID in quotes; '
2780                                         || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.';
2781                         new_owning_lib := new_owner::INTEGER;
2782                 PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib);
2783                 END IF;
2784         ELSE
2785                 RAISE WARNING 
2786                         '% is not a valid actor.org_unit shortname or ID; no change made.', 
2787                         new_owning_lib;
2788                 RETURN;
2789         END IF;
2790
2791 END;
2792
2793 $$ LANGUAGE plpgsql;
2794
2795 CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
2796
2797 use MARC::Record;
2798 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2799 use MARC::Charset;
2800
2801 MARC::Charset->assume_unicode(1);
2802
2803 my $xml = shift;
2804
2805 eval {
2806     my $r = MARC::Record->new_from_xml( $xml );
2807     my $output_xml = $r->as_xml_record();
2808 };
2809 if ($@) {
2810     return 0;
2811 } else {
2812     return 1;
2813 }
2814
2815 $func$ LANGUAGE PLPERLU;
2816 COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
2817
2818 CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
2819 BEGIN
2820    EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
2821            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2822            $$) TO '$$ ||  dir || $$/actor_hours_of_operation'$$;
2823    EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ ||
2824            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2825            $$) TO '$$ ||  dir || $$/actor_org_unit_closed'$$;
2826    EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ ||
2827            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2828            $$) TO '$$ ||  dir || $$/actor_org_unit_setting'$$;
2829    EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ ||
2830            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2831            $$) TO '$$ ||  dir || $$/asset_copy_location'$$;
2832    EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ ||
2833            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2834            $$) TO '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2835    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ ||
2836            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2837            $$) TO '$$ ||  dir || $$/asset_call_number_prefix'$$;
2838    EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ ||
2839            ARRAY_TO_STRING(orgs, ',') || $$)$$ ||
2840            $$) TO '$$ ||  dir || $$/asset_call_number_suffix'$$;
2841    EXECUTE $$COPY config.rule_circ_duration TO '$$ ||  dir || $$/config_rule_circ_duration'$$;
2842    EXECUTE $$COPY config.rule_age_hold_protect TO '$$ ||  dir || $$/config_rule_age_hold_protect'$$;
2843    EXECUTE $$COPY config.rule_max_fine TO '$$ ||  dir || $$/config_rule_max_fine'$$;
2844    EXECUTE $$COPY config.rule_recurring_fine TO '$$ ||  dir || $$/config_rule_recurring_fine'$$;
2845    EXECUTE $$COPY permission.grp_tree TO '$$ ||  dir || $$/permission_grp_tree'$$;
2846 END;
2847 $FUNC$ LANGUAGE PLPGSQL;
2848
2849 CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$
2850 BEGIN
2851    EXECUTE $$COPY actor.hours_of_operation FROM '$$ ||  dir || $$/actor_hours_of_operation'$$;
2852    EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ ||  dir || $$/actor_org_unit_closed'$$;
2853    EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ ||  dir || $$/actor_org_unit_setting'$$;
2854    EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ ||  dir || $$/asset_copy_location'$$;
2855    EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ ||  dir || $$/permission_grp_penalty_threshold'$$;
2856    EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_prefix'$$;
2857    EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ ||  dir || $$/asset_call_number_suffix'$$;
2858
2859    -- import any new circ rules
2860    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name');
2861    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name');
2862    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name');
2863    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name');
2864
2865    -- and permission groups
2866    PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name');
2867
2868 END;
2869 $FUNC$ LANGUAGE PLPGSQL;
2870
2871
2872 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$
2873 DECLARE
2874     name TEXT;
2875     loopq TEXT;
2876     existsq TEXT;
2877     ct INTEGER;
2878     cols TEXT[];
2879     copyst TEXT;
2880 BEGIN
2881     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
2882     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
2883     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
2884     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
2885     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
2886     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
2887     FOR name IN EXECUTE loopq LOOP
2888        EXECUTE existsq INTO ct USING name;
2889        IF ct = 0 THEN
2890            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
2891            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || 
2892                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
2893            EXECUTE copyst USING name;
2894        END IF;
2895     END LOOP;
2896 END;
2897 $FUNC$ LANGUAGE PLPGSQL;
2898
2899 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
2900 DECLARE
2901     id BIGINT;
2902     loopq TEXT;
2903     cols TEXT[];
2904     splitst TEXT;
2905 BEGIN
2906     loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
2907     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;
2908     FOR id IN EXECUTE loopq USING delimiter LOOP
2909        RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
2910        splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
2911                  ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
2912        EXECUTE splitst USING id, delimiter;
2913     END LOOP;
2914 END;
2915 $FUNC$ LANGUAGE PLPGSQL;
2916
2917 CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
2918
2919 use strict;
2920 use warnings;
2921
2922 use MARC::Record;
2923 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2924 use MARC::Charset;
2925
2926 MARC::Charset->assume_unicode(1);
2927
2928 my $target_xml = shift;
2929 my $source_xml = shift;
2930 my $tags = shift;
2931
2932 my $target;
2933 my $source;
2934
2935 eval { $target = MARC::Record->new_from_xml( $target_xml ); };
2936 if ($@) {
2937     return;
2938 }
2939 eval { $source = MARC::Record->new_from_xml( $source_xml ); };
2940 if ($@) {
2941     return;
2942 }
2943
2944 my $source_id = $source->subfield('901', 'c');
2945 $source_id = $source->subfield('903', 'a') unless $source_id;
2946 my $target_id = $target->subfield('901', 'c');
2947 $target_id = $target->subfield('903', 'a') unless $target_id;
2948
2949 my %existing_fields;
2950 foreach my $tag (@$tags) {
2951     my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
2952     my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
2953     $target->insert_fields_ordered(map { $_->clone() } @to_add);
2954     if (@to_add) {
2955         elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
2956     }
2957 }
2958
2959 my $xml = $target->as_xml_record;
2960 $xml =~ s/^<\?.+?\?>$//mo;
2961 $xml =~ s/\n//sgo;
2962 $xml =~ s/>\s+</></sgo;
2963
2964 return $xml;
2965
2966 $func$ LANGUAGE PLPERLU;
2967 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.';
2968
2969 CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
2970
2971 use strict;
2972 use warnings;
2973
2974 use MARC::Record;
2975 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2976 use Text::CSV;
2977
2978 my $in_tags = shift;
2979 my $in_values = shift;
2980
2981 # hack-and-slash parsing of array-passed-as-string;
2982 # this can go away once everybody is running Postgres 9.1+
2983 my $csv = Text::CSV->new({binary => 1});
2984 $in_tags =~ s/^{//;
2985 $in_tags =~ s/}$//;
2986 my $status = $csv->parse($in_tags);
2987 my $tags = [ $csv->fields() ];
2988 $in_values =~ s/^{//;
2989 $in_values =~ s/}$//;
2990 $status = $csv->parse($in_values);
2991 my $values = [ $csv->fields() ];
2992
2993 my $marc = MARC::Record->new();
2994
2995 $marc->leader('00000nam a22000007  4500');
2996 $marc->append_fields(MARC::Field->new('008', '000000s                       000   eng d'));
2997
2998 foreach my $i (0..$#$tags) {
2999     my ($tag, $sf);
3000     if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
3001         $tag = $1;
3002         $sf = $2;
3003         $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3004     } elsif ($tags->[$i] =~ /^(\d{3})$/) {
3005         $tag = $1;
3006         $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
3007     }
3008 }
3009
3010 my $xml = $marc->as_xml_record;
3011 $xml =~ s/^<\?.+?\?>$//mo;
3012 $xml =~ s/\n//sgo;
3013 $xml =~ s/>\s+</></sgo;
3014
3015 return $xml;
3016
3017 $func$ LANGUAGE PLPERLU;
3018 COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
3019 The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
3020 The second argument is an array of text containing the values to plug into each field.  
3021 If the value for a given field is NULL or the empty string, it is not inserted.
3022 $$;
3023
3024 CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
3025
3026 my ($marcxml, $tag, $pos, $value) = @_;
3027
3028 use MARC::Record;
3029 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3030 use MARC::Charset;
3031 use strict;
3032
3033 MARC::Charset->assume_unicode(1);
3034
3035 elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
3036 elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
3037 elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
3038 elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
3039
3040 my $xml = $marcxml;
3041 eval {
3042     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
3043
3044     foreach my $field ($marc->field($tag)) {
3045         $field->update("ind$pos" => $value);
3046     }
3047     $xml = $marc->as_xml_record;
3048     $xml =~ s/^<\?.+?\?>$//mo;
3049     $xml =~ s/\n//sgo;
3050     $xml =~ s/>\s+</></sgo;
3051 };
3052 return $xml;
3053
3054 $func$ LANGUAGE PLPERLU;
3055
3056 COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
3057 The first argument is a MARCXML string.
3058 The second argument is a MARC tag.
3059 The third argument is the indicator position, either 1 or 2.
3060 The fourth argument is the character to set the indicator value to.
3061 All occurences of the specified field will be changed.
3062 The function returns the revised MARCXML string.$$;
3063
3064 CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
3065     username TEXT,
3066     password TEXT,
3067     org TEXT,
3068     perm_group TEXT,
3069     first_name TEXT DEFAULT '',
3070     last_name TEXT DEFAULT ''
3071 ) RETURNS VOID AS $func$
3072 BEGIN
3073     RAISE NOTICE '%', org ;
3074     INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
3075     SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
3076     FROM   actor.org_unit aou, permission.grp_tree pgt
3077     WHERE  aou.shortname = org
3078     AND    pgt.name = perm_group;
3079 END
3080 $func$
3081 LANGUAGE PLPGSQL;
3082
3083 -- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
3084 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
3085     DECLARE
3086         target_event_def ALIAS FOR $1;
3087         orgs ALIAS FOR $2;
3088     BEGIN
3089         DROP TABLE IF EXISTS new_atevdefs;
3090         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3091         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3092             INSERT INTO action_trigger.event_definition (
3093                 active
3094                 ,owner
3095                 ,name
3096                 ,hook
3097                 ,validator
3098                 ,reactor
3099                 ,cleanup_success
3100                 ,cleanup_failure
3101                 ,delay
3102                 ,max_delay
3103                 ,usr_field
3104                 ,opt_in_setting
3105                 ,delay_field
3106                 ,group_field
3107                 ,template
3108                 ,granularity
3109                 ,repeat_delay
3110             ) SELECT
3111                 'f'
3112                 ,orgs[i]
3113                 ,name || ' (clone of '||target_event_def||')'
3114                 ,hook
3115                 ,validator
3116                 ,reactor
3117                 ,cleanup_success
3118                 ,cleanup_failure
3119                 ,delay
3120                 ,max_delay
3121                 ,usr_field
3122                 ,opt_in_setting
3123                 ,delay_field
3124                 ,group_field
3125                 ,template
3126                 ,granularity
3127                 ,repeat_delay
3128             FROM
3129                 action_trigger.event_definition
3130             WHERE
3131                 id = target_event_def
3132             ;
3133             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3134             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3135             INSERT INTO action_trigger.environment (
3136                 event_def
3137                 ,path
3138                 ,collector
3139                 ,label
3140             ) SELECT
3141                 currval('action_trigger.event_definition_id_seq')
3142                 ,path
3143                 ,collector
3144                 ,label
3145             FROM
3146                 action_trigger.environment
3147             WHERE
3148                 event_def = target_event_def
3149             ;
3150             INSERT INTO action_trigger.event_params (
3151                 event_def
3152                 ,param
3153                 ,value
3154             ) SELECT
3155                 currval('action_trigger.event_definition_id_seq')
3156                 ,param
3157                 ,value
3158             FROM
3159                 action_trigger.event_params
3160             WHERE
3161                 event_def = target_event_def
3162             ;
3163         END LOOP;
3164         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);
3165     END;
3166 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3167
3168 -- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
3169 CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
3170     DECLARE
3171         target_event_def ALIAS FOR $1;
3172         orgs ALIAS FOR $2;
3173         new_interval ALIAS FOR $3;
3174     BEGIN
3175         DROP TABLE IF EXISTS new_atevdefs;
3176         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3177         FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
3178             INSERT INTO action_trigger.event_definition (
3179                 active
3180                 ,owner
3181                 ,name
3182                 ,hook
3183                 ,validator
3184                 ,reactor
3185                 ,cleanup_success
3186                 ,cleanup_failure
3187                 ,delay
3188                 ,max_delay
3189                 ,usr_field
3190                 ,opt_in_setting
3191                 ,delay_field
3192                 ,group_field
3193                 ,template
3194                 ,granularity
3195                 ,repeat_delay
3196             ) SELECT
3197                 'f'
3198                 ,orgs[i]
3199                 ,name || ' (clone of '||target_event_def||')'
3200                 ,hook
3201                 ,validator
3202                 ,reactor
3203                 ,cleanup_success
3204                 ,cleanup_failure
3205                 ,new_interval
3206                 ,max_delay
3207                 ,usr_field
3208                 ,opt_in_setting
3209                 ,delay_field
3210                 ,group_field
3211                 ,template
3212                 ,granularity
3213                 ,repeat_delay
3214             FROM
3215                 action_trigger.event_definition
3216             WHERE
3217                 id = target_event_def
3218             ;
3219             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3220             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3221             INSERT INTO action_trigger.environment (
3222                 event_def
3223                 ,path
3224                 ,collector
3225                 ,label
3226             ) SELECT
3227                 currval('action_trigger.event_definition_id_seq')
3228                 ,path
3229                 ,collector
3230                 ,label
3231             FROM
3232                 action_trigger.environment
3233             WHERE
3234                 event_def = target_event_def
3235             ;
3236             INSERT INTO action_trigger.event_params (
3237                 event_def
3238                 ,param
3239                 ,value
3240             ) SELECT
3241                 currval('action_trigger.event_definition_id_seq')
3242                 ,param
3243                 ,value
3244             FROM
3245                 action_trigger.event_params
3246             WHERE
3247                 event_def = target_event_def
3248             ;
3249         END LOOP;
3250         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);
3251     END;
3252 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
3253
3254 -- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
3255 CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
3256     DECLARE
3257         org ALIAS FOR $1;
3258         target_event_defs ALIAS FOR $2;
3259     BEGIN
3260         DROP TABLE IF EXISTS new_atevdefs;
3261         CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
3262         FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP
3263             INSERT INTO action_trigger.event_definition (
3264                 active
3265                 ,owner
3266                 ,name
3267                 ,hook
3268                 ,validator
3269                 ,reactor
3270                 ,cleanup_success
3271                 ,cleanup_failure
3272                 ,delay
3273                 ,max_delay
3274                 ,usr_field
3275                 ,opt_in_setting
3276                 ,delay_field
3277                 ,group_field
3278                 ,template
3279                 ,granularity
3280                 ,repeat_delay
3281             ) SELECT
3282                 'f'
3283                 ,org
3284                 ,name || ' (clone of '||target_event_defs[i]||')'
3285                 ,hook
3286                 ,validator
3287                 ,reactor
3288                 ,cleanup_success
3289                 ,cleanup_failure
3290                 ,delay
3291                 ,max_delay
3292                 ,usr_field
3293                 ,opt_in_setting
3294                 ,delay_field
3295                 ,group_field
3296                 ,template
3297                 ,granularity
3298                 ,repeat_delay
3299             FROM
3300                 action_trigger.event_definition
3301             WHERE
3302                 id = target_event_defs[i]
3303             ;
3304             RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
3305             INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
3306             INSERT INTO action_trigger.environment (
3307                 event_def
3308                 ,path
3309                 ,collector
3310                 ,label
3311             ) SELECT
3312                 currval('action_trigger.event_definition_id_seq')
3313                 ,path
3314                 ,collector
3315                 ,label
3316             FROM
3317                 action_trigger.environment
3318             WHERE