Changeset 9458 for trunk/Open-ILS/src/sql/Pg/upgrade_simple_record.sql

Timestamp:

04/26/08 12:49:25 (2 months ago)

Author:

miker

Message:

base materialized simple record on old super_simple_record

Files:

• trunk/Open-ILS/src/sql/Pg/upgrade_simple_record.sql (modified) (3 diffs)

trunk/Open-ILS/src/sql/Pg/upgrade_simple_record.sql

@@ -2 +2 @@
 BEGIN;
 
-CREATE TABLE reporter.materialized_simple_record AS SELECT * FROM reporter.simple_record WHERE 1=0;
+CREATE TABLE reporter.materialized_simple_record AS SELECT * FROM reporter.super_simple_record WHERE 1=0;
 
 INSERT INTO reporter.materialized_simple_record
@@ -9 +9 @@
 
 ALTER TABLE reporter.materialized_simple_record ADD PRIMARY KEY (id);
+
+CREATE OR REPLACE VIEW reporter.super_simple_record AS SELECT * FROM reporter.materialized_simple_record;
+
+CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
+SELECT  r.id,
+    r.fingerprint,
+    r.quality,
+    r.tcn_source,
+    r.tcn_value,
+    title.value AS title,
+    FIRST(author.value) AS author,
+    publisher.value AS publisher,
+    SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
+    ARRAY_ACCUM( SUBSTRING(isbn.value FROM $$^\S+$$) ) AS isbn,
+    ARRAY_ACCUM( SUBSTRING(issn.value FROM $$^\S+$$) ) AS issn
+  FROM  biblio.record_entry r
+    LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
+    LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
+    LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
+    LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
+    LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
+    LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
+  WHERE r.deleted IS FALSE
+  GROUP BY 1,2,3,4,5,6,8,9;
 
 CREATE OR REPLACE FUNCTION reporter.simple_rec_sync () RETURNS TRIGGER AS $$
@@ -17 +41 @@
 
     IF TG_OP IN ('INSERT','UPDATE') AND NOT NEW.deleted THEN
-        INSERT INTO reporter.materialized_simple_record SELECT * FROM reporter.simple_record WHERE id = NEW.record;
+        INSERT INTO reporter.materialized_simple_record SELECT * FROM reporter.old_super_simple_record WHERE id = NEW.record;
     END IF;