Replaced TRUNCATE with DELETE FROM in migration_tools.create_cards
[migration-tools.git] / sql / base / base.sql
1 --------------------------------------------------------------------------
2 -- An example of how to use:
3 -- 
4 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo; 
5 -- \i base.sql
6 -- SELECT migration_tools.init('foo');
7 -- SELECT migration_tools.build('foo');
8 -- SELECT * FROM foo.fields_requiring_mapping;
9 -- \d foo.actor_usr
10 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS foo.asset_copy;
11 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
12 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
13
14 CREATE SCHEMA migration_tools;
15
16 CREATE OR REPLACE FUNCTION migration_tools.production_tables (TEXT) RETURNS TEXT[] AS $$
17     DECLARE
18         migration_schema ALIAS FOR $1;
19         output  RECORD;
20     BEGIN
21         FOR output IN
22             EXECUTE 'SELECT string_to_array(value,'','') AS tables FROM ' || migration_schema || '.config WHERE key = ''production_tables'';'
23         LOOP
24             RETURN output.tables;
25         END LOOP;
26     END;
27 $$ LANGUAGE PLPGSQL STRICT STABLE;
28
29 CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
30     DECLARE
31         migration_schema ALIAS FOR $1;
32         output TEXT;
33     BEGIN
34         FOR output IN
35             EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
36         LOOP
37             RETURN output;
38         END LOOP;
39     END;
40 $$ LANGUAGE PLPGSQL STRICT STABLE;
41
42
43 CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
44     DECLARE
45         migration_schema ALIAS FOR $1;
46         sql ALIAS FOR $2;
47         nrows ALIAS FOR $3;
48     BEGIN
49         EXECUTE 'INSERT INTO ' || migration_schema || '.sql_log ( sql, row_count ) VALUES ( ' || quote_literal(sql) || ', ' || nrows || ' );';
50     END;
51 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
52
53 CREATE OR REPLACE FUNCTION migration_tools.exec (TEXT,TEXT) RETURNS VOID AS $$
54     DECLARE
55         migration_schema ALIAS FOR $1;
56         sql ALIAS FOR $2;
57         nrows INTEGER;
58     BEGIN
59         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
60         --RAISE INFO '%', sql;
61         EXECUTE sql;
62         GET DIAGNOSTICS nrows = ROW_COUNT;
63         PERFORM migration_tools.log(migration_schema,sql,nrows);
64     EXCEPTION
65         WHEN OTHERS THEN 
66             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
67     END;
68 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
69
70 CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
71     DECLARE
72         migration_schema ALIAS FOR $1;
73         sql ALIAS FOR $2;
74         nrows INTEGER;
75     BEGIN
76         EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
77         RAISE INFO 'debug_exec sql = %', sql;
78         EXECUTE sql;
79         GET DIAGNOSTICS nrows = ROW_COUNT;
80         PERFORM migration_tools.log(migration_schema,sql,nrows);
81     EXCEPTION
82         WHEN OTHERS THEN 
83             RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
84     END;
85 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
86
87 CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
88     DECLARE
89         migration_schema ALIAS FOR $1;
90         sql TEXT;
91     BEGIN
92         EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
93         EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
94         EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
95         BEGIN
96             SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
97             EXECUTE sql;
98         EXCEPTION
99             WHEN OTHERS THEN 
100                 RAISE INFO '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
101         END;
102         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
103         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
104         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,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,action.circulation,action.hold_request,action.hold_notification,money.grocery,money.billing,money.cash_payment,money.forgive_payment'' );' );
105         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
106         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
107         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
108         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );  
109         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map ( 
110             id SERIAL,
111             perm_grp_id INTEGER,
112             transcribed_perm_group TEXT,
113             legacy_field1 TEXT,
114             legacy_value1 TEXT,
115             legacy_field2 TEXT,
116             legacy_value2 TEXT,
117             legacy_field3 TEXT,
118             legacy_value3 TEXT
119         );' );
120         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_profile_map'', ''base_profile_map'' );' );
121         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_item_dynamic_field_map;' );  
122         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_item_dynamic_field_map ( 
123             id SERIAL,
124             evergreen_field TEXT,
125             evergreen_value TEXT,
126             evergreen_datatype TEXT,
127             legacy_field1 TEXT,
128             legacy_value1 TEXT,
129             legacy_field2 TEXT,
130             legacy_value2 TEXT,
131             legacy_field3 TEXT,
132             legacy_value3 TEXT
133         );' );
134         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);' ); 
135         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);' ); 
136         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);' ); 
137         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_item_dynamic_field_map'', ''base_item_dynamic_field_map'' );' );
138         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_copy_location_map;' );  
139         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_copy_location_map ( 
140             id SERIAL,
141             location INTEGER,
142             holdable BOOLEAN NOT NULL DEFAULT TRUE,
143             hold_verify BOOLEAN NOT NULL DEFAULT FALSE,
144             opac_visible BOOLEAN NOT NULL DEFAULT TRUE,
145             circulate BOOLEAN NOT NULL DEFAULT TRUE,
146             transcribed_location TEXT,
147             legacy_field1 TEXT,
148             legacy_value1 TEXT,
149             legacy_field2 TEXT,
150             legacy_value2 TEXT,
151             legacy_field3 TEXT,
152             legacy_value3 TEXT
153         );' );
154         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);' ); 
155         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);' ); 
156         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);' ); 
157         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_loc_idx ON ' || migration_schema || '.base_copy_location_map (transcribed_location);' ); 
158         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_copy_location_map'', ''base_copy_location_map'' );' );
159         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_circ_field_map;' );  
160         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_circ_field_map ( 
161             id SERIAL,
162             circulate BOOLEAN,
163             loan_period TEXT,
164             max_renewals TEXT,
165             max_out TEXT,
166             fine_amount TEXT,
167             fine_interval TEXT,
168             max_fine TEXT,
169             item_field1 TEXT,
170             item_value1 TEXT,
171             item_field2 TEXT,
172             item_value2 TEXT,
173             patron_field1 TEXT,
174             patron_value1 TEXT,
175             patron_field2 TEXT,
176             patron_value2 TEXT
177         );' );
178         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf1_idx ON ' || migration_schema || '.base_circ_field_map (item_field1,item_value1);' ); 
179         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf2_idx ON ' || migration_schema || '.base_circ_field_map (item_field2,item_value2);' ); 
180         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf3_idx ON ' || migration_schema || '.base_circ_field_map (patron_field1,patron_value1);' ); 
181         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf4_idx ON ' || migration_schema || '.base_circ_field_map (patron_field2,patron_value2);' ); 
182         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_circ_field_map'', ''base_circ_field_map'' );' );
183
184         BEGIN
185             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
186         EXCEPTION
187             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_init'';' );
188         END;
189     END;
190 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
191
192 CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
193     DECLARE
194         migration_schema ALIAS FOR $1;
195         production_tables TEXT[];
196     BEGIN
197         --RAISE INFO 'In migration_tools.build(%)', migration_schema;
198         SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
199         PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables);
200         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
201         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
202         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
203         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
204         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
205         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 );' );
206         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 );' );
207     END;
208 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
209
210 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
211     DECLARE
212         migration_schema ALIAS FOR $1;
213         production_tables ALIAS FOR $2;
214     BEGIN
215         --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
216         FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
217             PERFORM migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
218         END LOOP;
219     END;
220 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
221
222 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
223     DECLARE
224         migration_schema ALIAS FOR $1;
225         production_table ALIAS FOR $2;
226         base_staging_table TEXT;
227         columns RECORD;
228     BEGIN
229         base_staging_table = REPLACE( production_table, '.', '_' );
230         --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
231         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
232         PERFORM migration_tools.exec( $1, '
233             INSERT INTO ' || migration_schema || '.fields_requiring_mapping
234                 SELECT table_schema, table_name, column_name, data_type
235                 FROM information_schema.columns 
236                 WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
237         ' );
238         FOR columns IN 
239             SELECT table_schema, table_name, column_name, data_type
240             FROM information_schema.columns 
241             WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
242         LOOP
243             PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
244         END LOOP;
245     END;
246 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
247
248 CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
249     DECLARE
250         migration_schema ALIAS FOR $1;
251         production_tables TEXT[];
252     BEGIN
253         --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
254         SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
255         FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
256             PERFORM migration_tools.insert_into_production(migration_schema,production_tables[i]);
257         END LOOP;
258     END;
259 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
260
261 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
262     DECLARE
263         migration_schema ALIAS FOR $1;
264         production_table ALIAS FOR $2;
265         base_staging_table TEXT;
266         columns RECORD;
267     BEGIN
268         base_staging_table = REPLACE( production_table, '.', '_' );
269         --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
270         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';' );
271     END;
272 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
273
274 CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
275     DECLARE
276         full_name TEXT := $1;
277         temp TEXT;
278         family_name TEXT := '';
279         first_given_name TEXT := '';
280         second_given_name TEXT := '';
281         suffix TEXT := '';
282         prefix TEXT := '';
283     BEGIN
284         temp := full_name;
285         -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
286         IF temp ilike '%MR.%' THEN
287             prefix := 'Mr.';
288             temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
289         END IF;
290         IF temp ilike '%MRS.%' THEN
291             prefix := 'Mrs.';
292             temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
293         END IF;
294         IF temp ilike '%MS.%' THEN
295             prefix := 'Ms.';
296             temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
297         END IF;
298         IF temp ilike '%DR.%' THEN
299             prefix := 'Dr.';
300             temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
301         END IF;
302         IF temp ilike '%JR%' THEN
303             suffix := 'Jr.';
304             temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
305         END IF;
306         IF temp ilike '%JR,%' THEN
307             suffix := 'Jr.';
308             temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
309         END IF;
310         IF temp ilike '%SR%' THEN
311             suffix := 'Sr.';
312             temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
313         END IF;
314         IF temp ilike '%SR,%' THEN
315             suffix := 'Sr.';
316             temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
317         END IF;
318         IF temp ~ E'\\sII$' THEN
319             suffix := 'II';
320             temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
321         END IF;
322         IF temp ~ E'\\sIII$' THEN
323             suffix := 'III';
324             temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
325         END IF;
326         IF temp ~ E'\\sIV$' THEN
327             suffix := 'IV';
328             temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
329         END IF;
330
331         family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
332         first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
333         second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE ''  END );
334
335         RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
336     END;
337 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
338
339 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
340     DECLARE
341         city_state_zip TEXT := $1;
342         city TEXT := '';
343         state TEXT := '';
344         zip TEXT := '';
345     BEGIN
346         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;
347         city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
348         IF city_state_zip ~ ',' THEN
349             state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
350             city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
351         ELSE
352             IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*' THEN
353                 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*.*$', E'\\1' );
354                 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*.*)$', E'\\1\\2' );
355             ELSE
356                 IF city_state_zip ~ E'^\\S+$'  THEN
357                     city := city_state_zip;
358                     state := 'N/A';
359                 ELSE
360                     state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
361                     city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
362                 END IF;
363             END IF;
364         END IF;
365         RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
366     END;
367 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
368
369 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
370     DECLARE
371         n TEXT := o;
372     BEGIN
373         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
374             IF o::BIGINT < t THEN
375                 n = o::BIGINT + t;
376             END IF;
377         END IF;
378
379         RETURN n;
380     END;
381 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
382
383 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
384     DECLARE
385         migration_schema ALIAS FOR $1;
386         output TEXT;
387     BEGIN
388         FOR output IN
389             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
390         LOOP
391             RETURN output;
392         END LOOP;
393     END;
394 $$ LANGUAGE PLPGSQL STRICT STABLE;
395
396 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
397     DECLARE
398         migration_schema ALIAS FOR $1;
399         output TEXT;
400     BEGIN
401         FOR output IN
402             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
403         LOOP
404             RETURN output;
405         END LOOP;
406     END;
407 $$ LANGUAGE PLPGSQL STRICT STABLE;
408
409 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
410     DECLARE
411         migration_schema ALIAS FOR $1;
412         output TEXT;
413     BEGIN
414         FOR output IN
415             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
416         LOOP
417             RETURN output;
418         END LOOP;
419     END;
420 $$ LANGUAGE PLPGSQL STRICT STABLE;
421
422 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
423     DECLARE
424         migration_schema ALIAS FOR $1;
425         output TEXT;
426     BEGIN
427         FOR output IN
428             EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
429         LOOP
430             RETURN output;
431         END LOOP;
432     END;
433 $$ LANGUAGE PLPGSQL STRICT STABLE;
434
435 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
436     DECLARE
437         migration_schema ALIAS FOR $1;
438         profile_map TEXT;
439         patron_table ALIAS FOR $2;
440         default_patron_profile ALIAS FOR $3;
441         sql TEXT;
442         sql_update TEXT;
443         sql_where1 TEXT := '';
444         sql_where2 TEXT := '';
445         sql_where3 TEXT := '';
446         output RECORD;
447     BEGIN
448         SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
449         FOR output IN 
450             EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
451         LOOP
452             sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
453             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);
454             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);
455             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);
456             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,'') || ';';
457             --RAISE INFO 'sql = %', sql;
458             PERFORM migration_tools.exec( $1, sql );
459         END LOOP;
460         PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;'  );
461         BEGIN
462             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
463         EXCEPTION
464             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
465         END;
466     END;
467 $$ LANGUAGE PLPGSQL STRICT STABLE;
468
469 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
470     DECLARE
471         migration_schema ALIAS FOR $1;
472         field_map TEXT;
473         item_table ALIAS FOR $2;
474         sql TEXT;
475         sql_update TEXT;
476         sql_where1 TEXT := '';
477         sql_where2 TEXT := '';
478         sql_where3 TEXT := '';
479         output RECORD;
480     BEGIN
481         SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
482         FOR output IN 
483             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
484         LOOP
485             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 ';
486             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);
487             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);
488             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);
489             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,'') || ';';
490             --RAISE INFO 'sql = %', sql;
491             PERFORM migration_tools.exec( $1, sql );
492         END LOOP;
493         BEGIN
494             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
495         EXCEPTION
496             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
497         END;
498     END;
499 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
500
501 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
502     DECLARE
503         migration_schema ALIAS FOR $1;
504         base_copy_location_map TEXT;
505         item_table ALIAS FOR $2;
506         sql TEXT;
507         sql_update TEXT;
508         sql_where1 TEXT := '';
509         sql_where2 TEXT := '';
510         sql_where3 TEXT := '';
511         output RECORD;
512     BEGIN
513         SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
514         FOR output IN 
515             EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
516         LOOP
517             sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
518             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);
519             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);
520             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);
521             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,'') || ';';
522             --RAISE INFO 'sql = %', sql;
523             PERFORM migration_tools.exec( $1, sql );
524         END LOOP;
525         BEGIN
526             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
527         EXCEPTION
528             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
529         END;
530     END;
531 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
532
533 -- 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
534 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
535     DECLARE
536         migration_schema ALIAS FOR $1;
537         field_map TEXT;
538         circ_table ALIAS FOR $2;
539         item_table ALIAS FOR $3;
540         patron_table ALIAS FOR $4;
541         sql TEXT;
542         sql_update TEXT;
543         sql_where1 TEXT := '';
544         sql_where2 TEXT := '';
545         sql_where3 TEXT := '';
546         sql_where4 TEXT := '';
547         output RECORD;
548     BEGIN
549         SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
550         FOR output IN 
551             EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
552         LOOP
553             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 ';
554             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);
555             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);
556             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);
557             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);
558             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,'') || ';';
559             --RAISE INFO 'sql = %', sql;
560             PERFORM migration_tools.exec( $1, sql );
561         END LOOP;
562         BEGIN
563             PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
564         EXCEPTION
565             WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
566         END;
567     END;
568 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
569
570 -- expand_barcode
571 --   $barcode      source barcode
572 --   $prefix       prefix to add to barcode, NULL = add no prefix
573 --   $maxlen       maximum length of barcode; default to 14 if left NULL
574 --   $pad          padding string to apply to left of source barcode before adding
575 --                 prefix and suffix; set to NULL or '' if no padding is desired
576 --   $suffix       suffix to add to barcode, NULL = add no suffix
577 --
578 -- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
579 -- If new barcode would be longer than $maxlen, the original barcode is returned instead.
580 --
581 CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
582     my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
583
584     # default case
585     return unless defined $barcode;
586
587     $prefix     = '' unless defined $prefix;
588     $maxlen ||= 14;
589     $pad        = '0' unless defined $pad;
590     $suffix     = '' unless defined $suffix;
591
592     # bail out if adding prefix and suffix would bring new barcode over max length
593     return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
594
595     my $new_barcode = $barcode;
596     if ($pad ne '') {
597         my $pad_length = $maxlen - length($prefix) - length($suffix);
598         if (length($barcode) < $pad_length) {
599             # assuming we always want padding on the left
600             # also assuming that it is possible to have the pad string be longer than 1 character
601             $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
602         }
603     }
604
605     # bail out if adding prefix and suffix would bring new barcode over max length
606     return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
607
608     return "$prefix$new_barcode$suffix";
609 $$ LANGUAGE PLPERL STABLE;
610
611 CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$
612     DECLARE
613         attempt_value ALIAS FOR $1;
614         datatype ALIAS FOR $2;
615         fail_value ALIAS FOR $3;
616         output RECORD;
617     BEGIN
618         FOR output IN
619             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;'
620         LOOP
621             RETURN output;
622         END LOOP;
623     EXCEPTION
624         WHEN OTHERS THEN
625             FOR output IN
626                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::' || datatype || ' AS a;'
627             LOOP
628                 RETURN output;
629             END LOOP;
630     END;
631 $$ LANGUAGE PLPGSQL STRICT STABLE;
632
633 CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
634     DECLARE
635         attempt_value ALIAS FOR $1;
636         fail_value ALIAS FOR $2;
637         output DATE;
638     BEGIN
639         FOR output IN
640             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::date AS a;'
641         LOOP
642             RETURN output;
643         END LOOP;
644     EXCEPTION
645         WHEN OTHERS THEN
646             FOR output IN
647                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
648             LOOP
649                 RETURN output;
650             END LOOP;
651     END;
652 $$ LANGUAGE PLPGSQL STRICT STABLE;
653
654 CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
655     DECLARE
656         attempt_value ALIAS FOR $1;
657         fail_value ALIAS FOR $2;
658         output TIMESTAMPTZ;
659     BEGIN
660         FOR output IN
661             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
662         LOOP
663             RETURN output;
664         END LOOP;
665     EXCEPTION
666         WHEN OTHERS THEN
667             FOR output IN
668                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
669             LOOP
670                 RETURN output;
671             END LOOP;
672     END;
673 $$ LANGUAGE PLPGSQL STRICT STABLE;
674
675 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
676     DECLARE
677         attempt_value ALIAS FOR $1;
678         fail_value ALIAS FOR $2;
679         output NUMERIC(8,2);
680     BEGIN
681         FOR output IN
682             EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;'
683         LOOP
684             RETURN output;
685         END LOOP;
686     EXCEPTION
687         WHEN OTHERS THEN
688             FOR output IN
689                 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
690             LOOP
691                 RETURN output;
692             END LOOP;
693     END;
694 $$ LANGUAGE PLPGSQL STRICT STABLE;
695
696 -- add_codabar_checkdigit
697 --   $barcode      source barcode
698 --
699 -- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
700 -- character with a checkdigit computed according to the usual algorithm for library barcodes
701 -- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>.  If the
702 -- input string does not meet those requirements, it is returned unchanged.
703 --
704 CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
705     my $barcode = shift;
706
707     return $barcode if $barcode !~ /^\d{13,14}$/;
708     $barcode = substr($barcode, 0, 13); # ignore 14th digit
709     my @digits = split //, $barcode;
710     my $total = 0;
711     $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
712     $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
713     my $remainder = $total % 10;
714     my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
715     return $barcode . $checkdigit; 
716 $$ LANGUAGE PLPERL STRICT STABLE;
717
718 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
719   DECLARE
720     phone TEXT := $1;
721     areacode TEXT := $2;
722     temp TEXT := '';
723     output TEXT := '';
724     n_digits INTEGER := 0;
725   BEGIN
726     temp := phone;
727     temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
728     temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
729     n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
730     IF n_digits = 7 AND areacode <> '' THEN
731       temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
732       output := (areacode || '-' || temp);
733     ELSE
734       output := temp;
735     END IF;
736     RETURN output;
737   END;
738
739 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
740
741 CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
742   my ($marcxml, $pos, $value) = @_;
743
744   use MARC::Record;
745   use MARC::File::XML;
746
747   my $xml = $marcxml;
748   eval {
749     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
750     my $leader = $marc->leader();
751     substr($leader, $pos, 1) = $value;
752     $marc->leader($leader);
753     $xml = $marc->as_xml_record;
754     $xml =~ s/^<\?.+?\?>$//mo;
755     $xml =~ s/\n//sgo;
756     $xml =~ s/>\s+</></sgo;
757   };
758   return $xml;
759 $$ LANGUAGE PLPERLU STABLE;
760
761 CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
762   my ($marcxml, $pos, $value) = @_;
763
764   use MARC::Record;
765   use MARC::File::XML;
766
767   my $xml = $marcxml;
768   eval {
769     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
770     my $f008 = $marc->field('008');
771
772     if ($f008) {
773        my $field = $f008->data();
774        substr($field, $pos, 1) = $value;
775        $f008->update($field);
776        $xml = $marc->as_xml_record;
777        $xml =~ s/^<\?.+?\?>$//mo;
778        $xml =~ s/\n//sgo;
779        $xml =~ s/>\s+</></sgo;
780     }
781   };
782   return $xml;
783 $$ LANGUAGE PLPERLU STABLE;
784
785
786 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
787   DECLARE
788     profile ALIAS FOR $1;
789   BEGIN
790     RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
791   END;
792 $$ LANGUAGE PLPGSQL STRICT STABLE;
793
794
795 CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
796   BEGIN
797     RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
798   END;
799 $$ LANGUAGE PLPGSQL STRICT STABLE;
800
801
802 CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
803
804   my ($marcxml, $tags) = @_;
805
806   use MARC::Record;
807   use MARC::File::XML;
808
809   my $xml = $marcxml;
810
811   eval {
812     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
813     my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
814
815     my @incumbents = ();
816
817     foreach my $field ( $marc->fields() ) {
818       push @incumbents, $field->as_formatted();
819     }
820
821     foreach $field ( $to_insert->fields() ) {
822       if (!grep {$_ eq $field->as_formatted()} @incumbents) {
823         $marc->insert_fields_ordered( ($field) );
824       }
825     }
826
827     $xml = $marc->as_xml_record;
828     $xml =~ s/^<\?.+?\?>$//mo;
829     $xml =~ s/\n//sgo;
830     $xml =~ s/>\s+</></sgo;
831   };
832
833   return $xml;
834
835 $$ LANGUAGE PLPERLU STABLE;
836
837 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
838
839 -- Usage:
840 --
841 --   First make sure the circ matrix is loaded and the circulations
842 --   have been staged to the extent possible (but at the very least
843 --   circ_lib, target_copy, usr, and *_renewal).  User profiles and
844 --   circ modifiers must also be in place.
845 --
846 --   SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
847 --
848
849 DECLARE
850   circ_lib             INT;
851   target_copy          INT;
852   usr                  INT;
853   is_renewal           BOOLEAN;
854   this_duration_rule   INT;
855   this_fine_rule       INT;
856   this_max_fine_rule   INT;
857   rcd                  config.rule_circ_duration%ROWTYPE;
858   rrf                  config.rule_recurring_fine%ROWTYPE;
859   rmf                  config.rule_max_fine%ROWTYPE;
860   circ                 INT;
861   n                    INT := 0;
862   n_circs              INT;
863   
864 BEGIN
865
866   EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
867
868   FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
869
870     -- Fetch the correct rules for this circulation
871     EXECUTE ('
872       SELECT
873         circ_lib,
874         target_copy,
875         usr,
876         CASE
877           WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
878           ELSE FALSE
879         END
880       FROM ' || tablename || ' WHERE id = ' || circ || ';')
881       INTO circ_lib, target_copy, usr, is_renewal ;
882     SELECT
883       INTO this_duration_rule,
884            this_fine_rule,
885            this_max_fine_rule
886       duration_rule,
887       recurring_fine_rule,
888       max_fine_rule
889       FROM action.find_circ_matrix_matchpoint(
890         circ_lib,
891         target_copy,
892         usr,
893         is_renewal
894         );
895     SELECT INTO rcd * FROM config.rule_circ_duration
896       WHERE id = this_duration_rule;
897     SELECT INTO rrf * FROM config.rule_recurring_fine
898       WHERE id = this_fine_rule;
899     SELECT INTO rmf * FROM config.rule_max_fine
900       WHERE id = this_max_fine_rule;
901
902     -- Apply the rules to this circulation
903     EXECUTE ('UPDATE ' || tablename || ' c
904     SET
905       duration_rule = rcd.name,
906       recurring_fine_rule = rrf.name,
907       max_fine_rule = rmf.name,
908       duration = rcd.normal,
909       recurring_fine = rrf.normal,
910       max_fine =
911         CASE rmf.is_percent
912           WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
913           ELSE rmf.amount
914         END,
915       renewal_remaining = rcd.max_renewals
916     FROM
917       config.rule_circ_duration rcd,
918       config.rule_recurring_fine rrf,
919       config.rule_max_fine rmf,
920                         asset.copy ac
921     WHERE
922       rcd.id = ' || this_duration_rule || ' AND
923       rrf.id = ' || this_fine_rule || ' AND
924       rmf.id = ' || this_max_fine_rule || ' AND
925                         ac.id = c.target_copy AND
926       c.id = ' || circ || ';');
927
928     -- Keep track of where we are in the process
929     n := n + 1;
930     IF (n % 100 = 0) THEN
931       RAISE INFO '%', n || ' of ' || n_circs
932         || ' (' || (100*n/n_circs) || '%) circs updated.';
933     END IF;
934
935   END LOOP;
936
937   RETURN;
938 END;
939
940 $$ LANGUAGE plpgsql;
941
942 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
943
944 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
945 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
946
947 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
948 -- TODO: Add a similar tool for actor stat cats, which behave differently.
949
950 DECLARE
951         c                    TEXT := schemaname || '.asset_copy_legacy';
952         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
953         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
954         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
955         stat_cat                                                 INT;
956   stat_cat_entry       INT;
957   
958 BEGIN
959
960   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
961
962                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
963
964                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
965                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
966                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
967
968   END LOOP;
969
970   RETURN;
971 END;
972
973 $$ LANGUAGE plpgsql;
974
975 CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
976
977 -- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
978 --        This will assign standing penalties as needed.
979
980 DECLARE
981   org_unit  INT;
982   usr       INT;
983
984 BEGIN
985
986   FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
987
988     FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
989   
990       EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
991
992     END LOOP;
993
994   END LOOP;
995
996   RETURN;
997
998 END;
999
1000 $$ LANGUAGE plpgsql;
1001
1002
1003 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
1004
1005 BEGIN
1006   INSERT INTO metabib.metarecord (fingerprint, master_record)
1007     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1008       FROM  biblio.record_entry b
1009       WHERE NOT b.deleted
1010         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)
1011         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1012       ORDER BY b.fingerprint, b.quality DESC;
1013   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1014     SELECT  m.id, r.id
1015       FROM  biblio.record_entry r
1016       JOIN  metabib.metarecord m USING (fingerprint)
1017      WHERE  NOT r.deleted;
1018 END;
1019   
1020 $$ LANGUAGE plpgsql;
1021
1022
1023 CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
1024
1025 BEGIN
1026   INSERT INTO metabib.metarecord (fingerprint, master_record)
1027     SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
1028       FROM  biblio.record_entry b
1029       WHERE NOT b.deleted
1030         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)
1031         AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
1032       ORDER BY b.fingerprint, b.quality DESC;
1033   INSERT INTO metabib.metarecord_source_map (metarecord, source)
1034     SELECT  m.id, r.id
1035       FROM  biblio.record_entry r
1036         JOIN metabib.metarecord m USING (fingerprint)
1037       WHERE NOT r.deleted
1038         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);
1039 END;
1040     
1041 $$ LANGUAGE plpgsql;
1042
1043
1044 CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
1045
1046 -- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
1047 --        Then SELECT migration_tools.create_cards('m_foo');
1048
1049 DECLARE
1050         u                    TEXT := schemaname || '.actor_usr_legacy';
1051         c                    TEXT := schemaname || '.actor_card';
1052   
1053 BEGIN
1054
1055         EXECUTE ('DELETE FROM ' || c || ';');
1056         EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
1057         EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
1058
1059   RETURN;
1060
1061 END;
1062
1063 $$ LANGUAGE plpgsql;
1064
1065
1066 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
1067
1068   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
1069
1070   my ($marcxml, $shortname) = @_;
1071
1072   use MARC::Record;
1073   use MARC::File::XML;
1074
1075   my $xml = $marcxml;
1076
1077   eval {
1078     my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
1079
1080     foreach my $field ( $marc->field('856') ) {
1081       if ( scalar(grep( /(netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
1082            ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
1083         $field->add_subfields( '9' => $shortname );
1084                                 $field->update( ind2 => '0');
1085       }
1086     }
1087
1088     $xml = $marc->as_xml_record;
1089     $xml =~ s/^<\?.+?\?>$//mo;
1090     $xml =~ s/\n//sgo;
1091     $xml =~ s/>\s+</></sgo;
1092   };
1093
1094   return $xml;
1095
1096 $$ LANGUAGE PLPERLU STABLE;
1097
1098 CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT) RETURNS VOID AS $$
1099
1100 DECLARE
1101   old_volume   BIGINT;
1102   new_volume   BIGINT;
1103   bib          BIGINT;
1104   owner        INTEGER;
1105   old_label    TEXT;
1106   remainder    BIGINT;
1107
1108 BEGIN
1109
1110   -- Bail out if asked to change the label to ##URI##
1111   IF new_label = '##URI##' THEN
1112     RETURN;
1113   END IF;
1114
1115   -- Gather information
1116   SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
1117   SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
1118   SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
1119
1120   -- Bail out if the label already is ##URI##
1121   IF old_label = '##URI##' THEN
1122     RETURN;
1123   END IF;
1124
1125   -- Bail out if the call number label is already correct
1126   IF new_volume = old_volume THEN
1127     RETURN;
1128   END IF;
1129
1130   -- Check whether we already have a destination volume available
1131   SELECT id INTO new_volume FROM asset.call_number 
1132     WHERE 
1133       record = bib AND
1134       owning_lib = owner AND
1135       label = new_label AND
1136       NOT deleted;
1137
1138   -- Create destination volume if needed
1139   IF NOT FOUND THEN
1140     INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) 
1141       VALUES (1, 1, bib, owner, new_label);
1142     SELECT id INTO new_volume FROM asset.call_number
1143       WHERE 
1144         record = bib AND
1145         owning_lib = owner AND
1146         label = new_label AND
1147         NOT deleted;
1148   END IF;
1149
1150   -- Move copy to destination
1151   UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
1152
1153   -- Delete source volume if it is now empty
1154   SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
1155   IF NOT FOUND THEN
1156     DELETE FROM asset.call_number WHERE id = old_volume;
1157   END IF;
1158
1159 END;
1160
1161 $$ LANGUAGE plpgsql;
1162
1163 CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
1164
1165         my $input = $_[0];
1166         my %zipdata;
1167
1168         open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
1169
1170         while (<FH>) {
1171                 chomp;
1172                 my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
1173                 $zipdata{$zip} = [$city, $state, $county];
1174         }
1175
1176         if (defined $zipdata{$input}) {
1177                 my ($city, $state, $county) = @{$zipdata{$input}};
1178                 return [$city, $state, $county];
1179         } elsif (defined $zipdata{substr $input, 0, 5}) {
1180                 my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
1181                 return [$city, $state, $county];
1182         } else {
1183                 return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
1184         }
1185   
1186 $$ LANGUAGE PLPERLU STABLE;
1187
1188 CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
1189
1190 DECLARE
1191   ou  INT;
1192         org_unit_depth INT;
1193         ou_parent INT;
1194         parent_depth INT;
1195   errors_found BOOLEAN;
1196         ou_shortname TEXT;
1197         parent_shortname TEXT;
1198         ou_type_name TEXT;
1199         parent_type TEXT;
1200         type_id INT;
1201         type_depth INT;
1202         type_parent INT;
1203         type_parent_depth INT;
1204         proper_parent TEXT;
1205
1206 BEGIN
1207
1208         errors_found := FALSE;
1209
1210 -- Checking actor.org_unit_type
1211
1212         FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
1213
1214                 SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
1215                 SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
1216
1217                 IF type_parent IS NOT NULL THEN
1218
1219                         SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
1220
1221                         IF type_depth - type_parent_depth <> 1 THEN
1222                                 SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
1223                                 SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
1224                                 RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
1225                                         ou_type_name, type_depth, parent_type, type_parent_depth;
1226                                 errors_found := TRUE;
1227
1228                         END IF;
1229
1230                 END IF;
1231
1232         END LOOP;
1233
1234 -- Checking actor.org_unit
1235
1236   FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
1237
1238                 SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
1239                 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;
1240                 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;
1241                 SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
1242                 SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
1243                 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;
1244                 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;
1245
1246                 IF ou_parent IS NOT NULL THEN
1247
1248                         IF      (org_unit_depth - parent_depth <> 1) OR (
1249                                 (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
1250                         ) THEN
1251                                 RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).', 
1252                                         ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
1253                                 errors_found := TRUE;
1254                         END IF;
1255
1256                 END IF;
1257
1258   END LOOP;
1259
1260         IF NOT errors_found THEN
1261                 RAISE INFO 'No errors found.';
1262         END IF;
1263
1264   RETURN;
1265
1266 END;
1267
1268 $$ LANGUAGE plpgsql;
1269
1270
1271 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
1272
1273 BEGIN   
1274
1275         DELETE FROM asset.opac_visible_copies;
1276
1277         INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
1278                 SELECT
1279                         cp.id, cp.circ_lib, cn.record
1280                 FROM
1281                         asset.copy cp
1282                         JOIN asset.call_number cn ON (cn.id = cp.call_number)
1283                         JOIN actor.org_unit a ON (cp.circ_lib = a.id)
1284                         JOIN asset.copy_location cl ON (cp.location = cl.id)
1285                         JOIN config.copy_status cs ON (cp.status = cs.id)
1286                         JOIN biblio.record_entry b ON (cn.record = b.id)
1287                 WHERE 
1288                         NOT cp.deleted AND
1289                         NOT cn.deleted AND
1290                         NOT b.deleted AND
1291                         cs.opac_visible AND
1292                         cl.opac_visible AND
1293                         cp.opac_visible AND
1294                         a.opac_visible;
1295
1296 END;
1297
1298 $$ LANGUAGE plpgsql;