sql/base/base_migration_schema_procs.sql

   1 --------------------------------------------------------------------------
   2 -- An example of how to use:
   3 --
   4 -- DROP SCHEMA foo CASCADE; CREATE SCHEMA foo;
   5 -- \i base_migration_schema_procs.sql
   6 -- SELECT migration_tools.build_default_base_staging_tables('foo');
   7 -- SELECT * FROM migration_tools.fields_requiring_mapping;
   8 -- \d foo.actor_usr
   9 -- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS foo.asset_copy;
  10 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
  11 -- Then, to move into production, do: select migration_tools.insert_default_into_production('foo')
  12
  13 CREATE SCHEMA migration_tools;
  14
  15 CREATE TABLE migration_tools.config (
  16     key TEXT UNIQUE,
  17     value TEXT
  18 );
  19
  20 INSERT INTO migration_tools.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,money.grocery,money.billing,money.cash_payment,money.forgive_payment' );
  21
  22 CREATE OR REPLACE FUNCTION migration_tools.production_tables () RETURNS TEXT[] AS $$
  23     SELECT string_to_array(value,',') FROM migration_tools.config WHERE key = 'production_tables';
  24 $$ LANGUAGE SQL STRICT STABLE;
  25
  26 -- This table is informational; feel free to modify etc.
  27 DROP TABLE IF EXISTS migration_tools.fields_requiring_mapping;
  28 CREATE TABLE migration_tools.fields_requiring_mapping(
  29     table_schema TEXT,
  30     table_name TEXT,
  31     column_name TEXT,
  32     data_type TEXT
  33 );
  34
  35 CREATE OR REPLACE FUNCTION migration_tools.build_default_base_staging_tables (TEXT) RETURNS VOID AS $$
  36     DECLARE
  37         migration_schema ALIAS FOR $1;
  38         production_tables TEXT[];
  39     BEGIN
  40         --RAISE INFO 'In migration_tools.build_default_base_staging_tables(%)', migration_schema;
  41         SELECT migration_tools.production_tables() INTO STRICT production_tables;
  42         EXECUTE migration_tools.build_base_staging_tables(migration_schema,production_tables);
  43     END;
  44 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  45
  46 CREATE OR REPLACE FUNCTION migration_tools.build_base_staging_tables (TEXT,TEXT[]) RETURNS VOID AS $$
  47     DECLARE
  48         migration_schema ALIAS FOR $1;
  49         production_tables ALIAS FOR $2;
  50     BEGIN
  51         --RAISE INFO 'In migration_tools.build_base_staging_tables(%,%)', migration_schema, production_tables;
  52         FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
  53             EXECUTE migration_tools.build_specific_base_staging_table(migration_schema,production_tables[i]);
  54         END LOOP;
  55     END;
  56 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  57
  58 CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TEXT,TEXT) RETURNS VOID AS $$
  59     DECLARE
  60         migration_schema ALIAS FOR $1;
  61         production_table ALIAS FOR $2;
  62         base_staging_table TEXT;
  63         columns RECORD;
  64     BEGIN
  65         base_staging_table = REPLACE( production_table, '.', '_' );
  66         --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
  67         EXECUTE 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( like ' || production_table || ' including defaults excluding constraints );';
  68         INSERT INTO migration_tools.fields_requiring_mapping
  69             SELECT table_schema, table_name, column_name, data_type
  70             FROM information_schema.columns
  71             WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL;
  72         FOR columns IN
  73             SELECT table_schema, table_name, column_name, data_type
  74             FROM information_schema.columns
  75             WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
  76         LOOP
  77             EXECUTE 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;';
  78         END LOOP;
  79     END;
  80 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  81
  82 CREATE OR REPLACE FUNCTION migration_tools.insert_default_into_production (TEXT) RETURNS VOID AS $$
  83     DECLARE
  84         migration_schema ALIAS FOR $1;
  85         production_tables TEXT[];
  86     BEGIN
  87         --RAISE INFO 'In migration_tools.insert_into_production(%)', migration_schema;
  88         SELECT migration_tools.production_tables() INTO STRICT production_tables;
  89         FOR i IN array_lower(production_tables,1) .. array_upper(production_tables,1) LOOP
  90             EXECUTE migration_tools.insert_into_production(migration_schema,production_tables[i]);
  91         END LOOP;
  92     END;
  93 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  94
  95 CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RETURNS VOID AS $$
  96     DECLARE
  97         migration_schema ALIAS FOR $1;
  98         production_table ALIAS FOR $2;
  99         base_staging_table TEXT;
 100         columns RECORD;
 101     BEGIN
 102         base_staging_table = REPLACE( production_table, '.', '_' );
 103         --RAISE INFO 'In migration_tools.insert_into_production(%,%) -> %', migration_schema, production_table, base_staging_table;
 104         EXECUTE 'INSERT INTO ' || production_table || ' SELECT * FROM ' || migration_schema || '.' || base_staging_table || ';';
 105     END;
 106 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 107
 108 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
 109     DECLARE
 110         city_state_zip TEXT := $1;
 111         city TEXT := '';
 112         state TEXT := '';
 113         zip TEXT := '';
 114     BEGIN
 115         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;
 116         city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
 117         IF city_state_zip ~ ',' THEN
 118             state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
 119             city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
 120         ELSE
 121             IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*' THEN
 122                 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*.*$', E'\\1' );
 123                 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*.*)$', E'\\1\\2' );
 124             ELSE
 125                 IF city_state_zip ~ E'^\\S+$'  THEN
 126                     city := city_state_zip;
 127                     state := 'N/A';
 128                 ELSE
 129                     state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
 130                     city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
 131                 END IF;
 132             END IF;
 133         END IF;
 134         RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
 135     END;
 136 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 137