tweaks to quick_metarecord_map.sql

* used version from wiki, which provides same results as the
  previous version but performs better on large databases
* now works without editing (a vacuum cannot run inside of a transaction)
* don't do vacuum full, just a regular vacuum analyze

[1] http://evergreen-ils.org/dokuwiki/doku.php?id=scratchpad:random_magic_spells#how_to_generate_metarecords_for_a_newly_loaded_bib_batch

Signed-off-by: Galen Charlton <gmc@esilibrary.com>

git-svn-id: svn://svn.open-ils.org/ILS/trunk@20075 dcc99617-32d9-48b4-a31d-7c20da2025e4

diff --git a/Open-ILS/src/extras/import/quick_metarecord_map.sql b/Open-ILS/src/extras/import/quick_metarecord_map.sql
index be92039..a9b9f37 100644 (file)
--- a/Open-ILS/src/extras/import/quick_metarecord_map.sql
+++ b/Open-ILS/src/extras/import/quick_metarecord_map.sql
@@ -1,23 +1,32 @@
 BEGIN;
 
-INSERT INTO metabib.metarecord (fingerprint,master_record)
-       SELECT  fingerprint,id
-         FROM  (SELECT DISTINCT ON (fingerprint)
-                       fingerprint, id, quality
-                 FROM  biblio.record_entry
-                 ORDER BY fingerprint, quality desc) AS x
-         WHERE fingerprint IS NOT NULL
-            AND fingerprint NOT IN ( SELECT fingerprint FROM metabib.metarecord);
-
-INSERT INTO metabib.metarecord_source_map (metarecord,source)
-       SELECT  m.id, b.id
-         FROM  biblio.record_entry b
-               JOIN metabib.metarecord m ON (m.fingerprint = b.fingerprint)
-               LEFT JOIN metabib.metarecord_source_map s ON (b.id = s.source)
-      WHERE s.id IS NULL;
+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
+                );
 
 COMMIT;
 
-VACUUM FULL ANALYZE VERBOSE metabib.metarecord;
-VACUUM FULL ANALYZE VERBOSE metabib.metarecord_source_map;
+VACUUM ANALYZE VERBOSE metabib.metarecord;
+VACUUM ANALYZE VERBOSE metabib.metarecord_source_map;