Adding metarecord spells as functions for convenience

[migration-tools.git] / sql / base / base.sql

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 3119af2..3b3a7a4 100644 (file)
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -910,3 +910,76 @@ BEGIN
 END;
 
 $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
+
+-- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
+--        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
+
+-- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
+-- TODO: Add a similar tool for actor stat cats, which behave differently.
+
+DECLARE
+       c                    TEXT := schemaname || '.asset_copy_legacy';
+       sc                                                                       TEXT := schemaname || '.asset_stat_cat';
+       sce                                                                      TEXT := schemaname || '.asset_stat_cat_entry';
+       scecm                                                            TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
+       stat_cat                                                 INT;
+  stat_cat_entry       INT;
+  
+BEGIN
+
+  FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
+
+               EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
+
+               EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
+                                                       SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
+                                                       (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
+
+  END LOOP;
+
+  RETURN;
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
+
+BEGIN
+  INSERT INTO metabib.metarecord (fingerprint, master_record)
+    SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
+      FROM  biblio.record_entry b
+      WHERE NOT b.deleted
+        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)
+        AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
+      ORDER BY b.fingerprint, b.quality DESC;
+  INSERT INTO metabib.metarecord_source_map (metarecord, source)
+    SELECT  m.id, r.id
+      FROM  biblio.record_entry r
+      JOIN  metabib.metarecord m USING (fingerprint)
+     WHERE  NOT r.deleted;
+END;
+  
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
+
+BEGIN
+  INSERT INTO metabib.metarecord (fingerprint, master_record)
+    SELECT  DISTINCT ON (b.fingerprint) b.fingerprint, b.id
+      FROM  biblio.record_entry b
+      WHERE NOT b.deleted
+        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)
+        AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
+      ORDER BY b.fingerprint, b.quality DESC;
+  INSERT INTO metabib.metarecord_source_map (metarecord, source)
+    SELECT  m.id, r.id
+      FROM  biblio.record_entry r
+        JOIN metabib.metarecord m USING (fingerprint)
+      WHERE NOT r.deleted
+        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);
+END;
+    
+$$ LANGUAGE plpgsql;