sql/base/10-staging.sql

   1
   2 CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
   3
   4 -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
   5 --        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
   6
   7 -- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
   8 -- TODO: Add a similar tool for actor stat cats, which behave differently.
   9
  10 DECLARE
  11         c                    TEXT := schemaname || '.asset_copy_legacy';
  12         sc                                                                       TEXT := schemaname || '.asset_stat_cat';
  13         sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
  14         scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
  15         stat_cat                                                 INT;
  16   stat_cat_entry       INT;
  17
  18 BEGIN
  19
  20   FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
  21
  22                 EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
  23
  24                 EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
  25                                                         SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
  26                                                         (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
  27
  28   END LOOP;
  29
  30   RETURN;
  31 END;
  32
  33 $$ LANGUAGE plpgsql;
  34
  35 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$
  36 DECLARE
  37     name TEXT;
  38     loopq TEXT;
  39     existsq TEXT;
  40     ct INTEGER;
  41     cols TEXT[];
  42     copyst TEXT;
  43 BEGIN
  44     EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename;
  45     EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$;
  46     EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ ||  dir || '/' || schemaname || '_' || tablename || $$'$$;
  47     loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol;
  48     existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1';
  49     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol;
  50     FOR name IN EXECUTE loopq LOOP
  51        EXECUTE existsq INTO ct USING name;
  52        IF ct = 0 THEN
  53            RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name;
  54            copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') ||
  55                      ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1';
  56            EXECUTE copyst USING name;
  57        END IF;
  58     END LOOP;
  59 END;
  60 $FUNC$ LANGUAGE PLPGSQL;
  61
  62 CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
  63 DECLARE
  64     id BIGINT;
  65     loopq TEXT;
  66     cols TEXT[];
  67     splitst TEXT;
  68 BEGIN
  69     loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
  70     SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> 'id' AND column_name <> matchcol;
  71     FOR id IN EXECUTE loopq USING delimiter LOOP
  72        RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
  73        splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
  74                  ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
  75        EXECUTE splitst USING id, delimiter;
  76     END LOOP;
  77 END;
  78 $FUNC$ LANGUAGE PLPGSQL;