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