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