From 2b9e0ff2b769f58cdda42022fda0c7e2fd45131a Mon Sep 17 00:00:00 2001
From: Bill Erickson <berickxx@gmail.com>
Date: Thu, 11 May 2017 17:54:58 -0400
Subject: [PATCH] LP#1251394 Compressed and wide display entry VIEWs

flat_display_entry is one row per entry with all of the relevent info.
compressed_display_entry sits atop flat_display_entry and compresses the
values into JSON scalars and arrays depending on the 'multi' value.

Wide display entry is a tabular view of the well-known fields a la
reporter.simple_record.

Signed-off-by: Bill Erickson <berickxx@gmail.com>
Signed-off-by: Mike Rylander <mrylander@gmail.com>
---
 Open-ILS/examples/fm_IDL.xml                  | 60 +++++++++++++++-
 .../XXXX.schema.metabib-display-field.sql     | 68 +++++++++++++++----
 .../YYYY.data.metabib-display-field.sql       |  9 ++-
 .../ZZZZ.UNDO.metabib-display-field.sql       |  2 +
 4 files changed, 121 insertions(+), 18 deletions(-)

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 70ac77152e..4d944a8b37 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -3135,7 +3135,9 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 			<field reporter:label="SVF Attributes" name="attrs" oils_persist:virtual="true" reporter:datatype="link"/>
 			<field reporter:label="MVF Attributes" name="mattrs" oils_persist:virtual="true" reporter:datatype="link"/>
 			<field reporter:label="Display Fields" name="display_entries" oils_persist:virtual="true" reporter:datatype="link"/>
-			<field reporter:label="Flat Display Fields" name="flat_display_entries" oils_persist:virtual="true" reporter:datatype="link"/>
+			<field reporter:label="Flat Display Entries" name="flat_display_entries" oils_persist:virtual="true" reporter:datatype="link"/>
+			<field reporter:label="Compressed Display Entries" name="compressed_display_entries" oils_persist:virtual="true" reporter:datatype="link"/>
+			<field reporter:label="Wide Display Entries" name="wide_display_entry" oils_persist:virtual="true" reporter:datatype="link"/>
 		</fields>
 		<links>
 			<link field="owner" reltype="has_a" key="id" map="" class="aou"/>
@@ -3161,6 +3163,8 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 			<link field="source" reltype="has_a" key="id" map="" class="cbs"/>
 			<link field="display_entries" reltype="has_many" key="source" map="" class="mde"/>
 			<link field="flat_display_entries" reltype="has_many" key="source" map="" class="mfde"/>
+			<link field="compressed_display_entries" reltype="has_many" key="source" map="" class="mcde"/>
+			<link field="wide_display_entry" reltype="might_have" key="source" map="" class="mwde"/>
 		</links>
         <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
             <actions>
@@ -3746,11 +3750,13 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 		oils_persist:tablename="metabib.flat_display_entry"
 		oils_obj:fieldmapper="metabib::flat_display_entry" 
 		oils_persist:field_safe="true"
-		reporter:label="Flat Display Field Entry View" 
+		reporter:label="Flat Display Entry" 
 		oils_persist:readonly="true">
 		<fields>
 			<field name="source" reporter:datatype="id" />
 			<field name="name" reporter:datatype="text"/>
+			<field name="multi" reporter:datatype="bool"/>
+			<field name="label" reporter:datatype="text"/>
 			<field name="field" reporter:datatype="link"/>
 			<field name="value" reporter:datatype="text"/>
 		</fields>
@@ -3765,6 +3771,56 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 			</actions>
 		</permacrud>
 	</class>
+	<class id="mcde" controller="open-ils.cstore open-ils.pcrud" 
+		oils_persist:tablename="metabib.compressed_display_entry"
+		oils_obj:fieldmapper="metabib::compressed_display_entry" 
+		oils_persist:field_safe="true"
+		reporter:label="Compressed Display Entry" 
+		oils_persist:readonly="true">
+		<fields>
+			<field name="source" reporter:datatype="id" />
+			<field name="name" reporter:datatype="text"/>
+			<field name="multi" reporter:datatype="bool"/>
+			<field name="label" reporter:datatype="text"/>
+			<field name="field" reporter:datatype="link"/>
+			<field name="value" reporter:datatype="text"/>
+		</fields>
+		<links>
+			<link field="source" reltype="has_a" key="id" map="" class="bre"/>
+			<link field="field" reltype="has_a" key="id" map="" class="cmf"/>
+			<link field="name" reltype="has_a" key="name" map="" class="cdfm"/>
+		</links>
+		<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+			<actions>
+				<retrieve/>
+			</actions>
+		</permacrud>
+	</class>
+	<class id="mwde" controller="open-ils.cstore open-ils.pcrud" 
+		oils_persist:tablename="metabib.wide_display_entry"
+		oils_obj:fieldmapper="metabib::wide_display_entry" 
+		oils_persist:field_safe="true"
+		reporter:label="Wide Display Entry" 
+		oils_persist:readonly="true">
+		<fields oils_persist:primary="source">
+			<field name="source" reporter:label="Record ID" reporter:datatype="id" />
+			<field name="title" reporter:label="Title" reporter:datatype="text"/>
+			<field name="author" reporter:label="Author" reporter:datatype="text"/>
+			<field name="subject" reporter:label="Subject" reporter:datatype="text"/>
+			<field name="topic_subject" reporter:label="Topic Subject" reporter:datatype="text"/>
+			<field name="isbn" reporter:label="ISBN" reporter:datatype="text"/>
+			<!-- TODO add all well-known fields -->
+		</fields>
+		<links>
+			<link field="source" reltype="has_a" key="id" map="" class="bre"/>
+		</links>
+		<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+			<actions>
+				<retrieve/>
+			</actions>
+		</permacrud>
+	</class>
+
 	<class id="cdfm" controller="open-ils.cstore open-ils.pcrud" 
 		oils_persist:tablename="config.display_field_map"
 		oils_obj:fieldmapper="config::display_field_map"
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.metabib-display-field.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.metabib-display-field.sql
index 2d69e817ad..467ef60c4c 100644
--- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.metabib-display-field.sql
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.metabib-display-field.sql
@@ -5,6 +5,12 @@ ALTER TABLE config.metabib_field
     ADD COLUMN display_xpath TEXT, 
     ADD COLUMN display_field BOOL NOT NULL DEFAULT FALSE;
 
+CREATE TABLE config.display_field_map (
+    name    TEXT   PRIMARY KEY,
+    field   INTEGER REFERENCES config.metabib_field (id),
+    multi   BOOLEAN DEFAULT FALSE
+);
+
 CREATE TABLE metabib.display_entry (
     id      BIGSERIAL  PRIMARY KEY,
     source  BIGINT     NOT NULL REFERENCES biblio.record_entry (id),
@@ -12,30 +18,64 @@ CREATE TABLE metabib.display_entry (
     value   TEXT       NOT NULL
 );
 
-CREATE TABLE config.display_field_map (
-    name    TEXT   PRIMARY KEY,
-    field   INTEGER REFERENCES config.metabib_field (id),
-    multi   BOOLEAN DEFAULT FALSE
-);
+CREATE INDEX metabib_display_entry_field_idx ON metabib.display_entry (field);
+CREATE INDEX metabib_display_entry_source_idx ON metabib.display_entry (source);
 
+-- one row per display entry fleshed with field info
 CREATE VIEW metabib.flat_display_entry AS
     SELECT
         mde.source,
         cdfm.name,
-        mde.field,
-        CASE WHEN cdfm.multi THEN
+        cdfm.multi,
+        cmf.label,
+        cmf.id AS field,
+        mde.value
+    FROM metabib.display_entry mde
+    JOIN config.metabib_field cmf ON (cmf.id = mde.field)
+    JOIN config.display_field_map cdfm ON (cdfm.field = mde.field)
+;
+
+-- like flat_display_entry except values are compressed 
+-- into one row per display_field_map and JSON-ified.
+CREATE VIEW metabib.compressed_display_entry AS
+    SELECT 
+        source,
+        name,
+        multi,
+        label,
+        field,
+        CASE WHEN multi THEN
             TO_JSON(ARRAY_AGG(value))
         ELSE
             TO_JSON(MIN(value))
         END AS value
-    FROM metabib.display_entry mde
-    JOIN config.display_field_map cdfm ON (cdfm.field = mde.field)
-    GROUP BY 1, 2, 3;
+    FROM metabib.flat_display_entry
+    GROUP BY 1, 2, 3, 4, 5
+;
+
+-- TODO: expand to encompass all well-known fields
+CREATE VIEW metabib.wide_display_entry AS
+    SELECT 
+        bre.id AS source,
+        COALESCE(mcde_title.value, 'null') AS title,
+        COALESCE(mcde_author.value, 'null') AS author,
+        COALESCE(mcde_subject.value, 'null') AS subject,
+        COALESCE(mcde_topic_subject.value, 'null') AS topic_subject,
+        COALESCE(mcde_isbn.value, 'null') AS isbn
+    -- ensure one row per bre regardless of any display fields
+    FROM biblio.record_entry bre 
+    LEFT JOIN metabib.compressed_display_entry mcde_title 
+        ON (bre.id = mcde_title.source AND mcde_title.name = 'title')
+    LEFT JOIN metabib.compressed_display_entry mcde_author 
+        ON (bre.id = mcde_author.source AND mcde_author.name = 'author')
+    LEFT JOIN metabib.compressed_display_entry mcde_subject 
+        ON (bre.id = mcde_subject.source AND mcde_subject.name = 'subject')
+    LEFT JOIN metabib.compressed_display_entry mcde_topic_subject 
+        ON (bre.id = mcde_topic_subject.source AND mcde_topic_subject.name = 'topic_subject')
+    LEFT JOIN metabib.compressed_display_entry mcde_isbn 
+        ON (bre.id = mcde_isbn.source AND mcde_isbn.name = 'isbn')
+;
 
-CREATE INDEX metabib_display_entry_field_idx 
-    ON metabib.display_entry (field);
-CREATE INDEX metabib_display_entry_source_idx 
-    ON metabib.display_entry (source);
 
 CREATE OR REPLACE FUNCTION metabib.display_field_normalize_trigger () 
     RETURNS TRIGGER AS $$
diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.data.metabib-display-field.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.data.metabib-display-field.sql
index 7432f7ce5a..09de901806 100644
--- a/Open-ILS/src/sql/Pg/upgrade/YYYY.data.metabib-display-field.sql
+++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.data.metabib-display-field.sql
@@ -20,14 +20,19 @@ VALUES
         '//mods32:mods/mods32:subject'),
     (40, 'subject', 'display|topic_subject', 'mods32', TRUE, FALSE, FALSE,
         oils_i18n_gettext(40, 'Subject', 'cmf', 'label'),
-        '//mods32:mods/mods32:subject/mods32:topic')
+        '//mods32:mods/mods32:subject/mods32:topic'),
+    (41, 'identifier', 'display|isbn', 'marcxml', TRUE, FALSE, FALSE,
+        oils_i18n_gettext(41, 'ISBN', 'cmf', 'label'),
+        $$//marc:datafield[@tag='020']/marc:subfield[@code='a' or @code='z']$$)
+
 ;
 
 INSERT INTO config.display_field_map (name, field, multi) VALUES
     ('title', 37, FALSE),
     ('author', 38, FALSE),
     ('subject', 39, TRUE),
-    ('topic_subject', 40, TRUE)
+    ('topic_subject', 40, TRUE),
+    ('isbn', 41, TRUE)
 ;
 
 COMMIT;
diff --git a/Open-ILS/src/sql/Pg/upgrade/ZZZZ.UNDO.metabib-display-field.sql b/Open-ILS/src/sql/Pg/upgrade/ZZZZ.UNDO.metabib-display-field.sql
index 60c4cd1ae7..5997b973ad 100644
--- a/Open-ILS/src/sql/Pg/upgrade/ZZZZ.UNDO.metabib-display-field.sql
+++ b/Open-ILS/src/sql/Pg/upgrade/ZZZZ.UNDO.metabib-display-field.sql
@@ -292,6 +292,8 @@ DROP TRIGGER display_field_normalize_tgr ON metabib.display_entry;
 DROP FUNCTION metabib.display_field_normalize_trigger();
 DROP INDEX metabib.metabib_display_entry_source_idx;
 DROP INDEX metabib.metabib_display_entry_field_idx;
+DROP VIEW metabib.wide_display_entry;
+DROP VIEW metabib.compressed_display_entry;
 DROP VIEW metabib.flat_display_entry;
 DROP TABLE config.display_field_map;
 DROP TABLE metabib.display_entry;
-- 
2.43.2