adding reporter.simple_record

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

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index e625bc3..2fa1597 100644 (file)
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -2197,4 +2197,38 @@
                        <link field="folder" reltype="has_a" key="id" map="" class="rof"/>
                </links>
        </class>
+       <class id="rsr" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::simple_record" oils_persist:tablename="reporter.simple_record">
+               <fields oils_persist:primary="id">
+                       <field name="isnew" oils_obj:array_position="0" oils_persist:virtual="true" />
+                       <field name="ischanged" oils_obj:array_position="1" oils_persist:virtual="true" />
+                       <field name="isdeleted" oils_obj:array_position="2" oils_persist:virtual="true" />
+                       <field reporter:label="Record ID" name="id" oils_obj:array_position="3" oils_persist:virtual="false" reporter:datatype="int" />
+                       <field reporter:label="Metarecord" name="metarecord" oils_obj:array_position="4" oils_persist:virtual="false" />
+                       <field reporter:label="Fingerprint" name="fingerprint" oils_obj:array_position="5" oils_persist:virtual="false" />
+                       <field reporter:label="Overall Record Quality" name="quality" oils_obj:array_position="6" oils_persist:virtual="false" reporter:datatype="int"/>
+                       <field reporter:label="TCN Source" name="tcn_source" oils_obj:array_position="7" oils_persist:virtual="false" />
+                       <field reporter:label="TCN Value" name="tcn_value" oils_obj:array_position="8" oils_persist:virtual="false" />
+                       <field reporter:label="Title Proper (normalized)" name="title" oils_obj:array_position="9" oils_persist:virtual="false" />
+                       <field reporter:label="Uniform Title (normalized)" name="uniform_title" oils_obj:array_position="10" oils_persist:virtual="false" />
+                       <field reporter:label="Author (normalized)" name="author" oils_obj:array_position="11" oils_persist:virtual="false" />
+                       <field reporter:label="Publisher (normalized)" name="publisher" oils_obj:array_position="12" oils_persist:virtual="false" />
+                       <field reporter:label="Publication Year (normalized)" name="pubdate" oils_obj:array_position="13" oils_persist:virtual="false" reporter:datatype="int"/>
+                       <field reporter:label="Series Title (normalized)" name="series_title" oils_obj:array_position="14" oils_persist:virtual="false" />
+                       <field reporter:label="Series Statement (normalized)" name="series_statement" oils_obj:array_position="15" oils_persist:virtual="false" />
+                       <field reporter:label="Summary" name="summary (normalized)" oils_obj:array_position="16" oils_persist:virtual="false" />
+                       <field reporter:label="ISBN" name="isbn" oils_obj:array_position="17" oils_persist:virtual="false" />
+                       <field reporter:label="ISSN" name="issn" oils_obj:array_position="18" oils_persist:virtual="false" />
+                       <field reporter:label="Topic Subjects (normalized)" name="topic_subject" oils_obj:array_position="19" oils_persist:virtual="false" />
+                       <field reporter:label="Geographic Subjects (normalized)" name="geographic_subject" oils_obj:array_position="20" oils_persist:virtual="false" />
+                       <field reporter:label="Genres (normalized)" name="genre" oils_obj:array_position="21" oils_persist:virtual="false" />
+                       <field reporter:label="Personal Name Subjects (normalized)" name="name_subject" oils_obj:array_position="22" oils_persist:virtual="false" />
+                       <field reporter:label="Corporate Name Subjects (normalized)" name="corporate_subject" oils_obj:array_position="23" oils_persist:virtual="false" />
+                       <field reporter:label="External URI List (normalized)" name="external_uri" oils_obj:array_position="24" oils_persist:virtual="false" />
+                       <field reporter:label="Full Bibliographic record" name="biblo_record" oils_obj:array_position="25" oils_persist:virtual="true" />
+               </fields>
+               <links>
+                       <link field="metarecord" reltype="has_a" key="id" map="" class="mmr"/>
+                       <link field="biblio_record" reltype="might_have" key="id" map="" class="bre"/>
+               </links>
+       </class>
 </IDL>

diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql
index 2421580..f709f9d 100644 (file)
--- a/Open-ILS/src/sql/Pg/reporter-schema.sql
+++ b/Open-ILS/src/sql/Pg/reporter-schema.sql
@@ -86,5 +86,44 @@ CREATE TABLE reporter.schedule (
 CREATE INDEX rpt_sched_runner_idx ON reporter.schedule (runner);
 CREATE INDEX rpt_sched_folder_idx ON reporter.schedule (folder);
 
+CREATE OR REPLACE VIEW reporter.simple_record AS
+SELECT r.id,
+       s.metarecord,
+       r.fingerprint,
+       r.quality,
+       r.tcn_source,
+       r.tcn_value,
+       title.value AS title,
+       uniform_title.value AS uniform_title,
+       author.value AS author,
+       publisher.value AS publisher,
+       SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
+       series_title.value AS series_title,
+       series_statement.value AS series_statement,
+       summary.value AS summary,
+       ARRAY_ACCUM( SUBSTRING(isbn.value FROM $$^\S+$$) ) AS isbn,
+       ARRAY_ACCUM( SUBSTRING(issn.value FROM $$^\S+$$) ) AS issn,
+       ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
+       ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
+       ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
+       ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '600' AND subfield = 'a' AND record = r.id)) AS name_subject,
+       ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '610' AND subfield = 'a' AND record = r.id)) AS corporate_subject,
+       ARRAY((SELECT value FROM metabib.full_rec WHERE tag = '856' AND subfield IN ('3','y','u') AND record = r.id ORDER BY CASE WHEN subfield IN ('3','y') THEN 0 ELSE 1 END)) AS external_uri
+  FROM biblio.record_entry r
+       JOIN metabib.metarecord_source_map s ON (s.source = r.id)
+       LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
+       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 = '100' 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')
+       LEFT JOIN metabib.full_rec series_title ON (r.id = series_title.record AND series_title.tag IN ('830','440') AND series_title.subfield = 'a')
+       LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
+       LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
+  WHERE        r.deleted IS FALSE
+  GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
+
+
 COMMIT;