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