Changeset 9507 for branches/acq-experiment/Open-ILS/src/sql/Pg/reporter-schema.sql

Timestamp:

05/04/08 20:58:16 (5 months ago)

Author:

erickson

Message:

Merged revisions 9444-9445,9447-9450,9452-9453,9456,9458,9460-9462,9464-9474,9476-9477,9479,9481-9484,9486,9490,9492,9494,9496,9498,9505-9506 via svnmerge from
svn://svn.open-ils.org/ILS/trunk

........

r9444 | miker | 2008-04-23 22:30:21 -0400 (Wed, 23 Apr 2008) | 1 line

use 001 as the authority record number value

........

r9445 | miker | 2008-04-23 23:02:32 -0400 (Wed, 23 Apr 2008) | 1 line

initial vandelay schema

........

r9447 | miker | 2008-04-24 12:50:56 -0400 (Thu, 24 Apr 2008) | 1 line

adding vandelay IDL section

........

r9448 | miker | 2008-04-24 16:10:54 -0400 (Thu, 24 Apr 2008) | 1 line

add translation support to the, um, translation widget; add example es-us translation

........

r9449 | miker | 2008-04-24 16:29:54 -0400 (Thu, 24 Apr 2008) | 1 line

moving es-us to es to catch all es-*

........

r9450 | miker | 2008-04-24 16:39:34 -0400 (Thu, 24 Apr 2008) | 1 line

typo in translate

........

r9452 | erickson | 2008-04-25 16:33:10 -0400 (Fri, 25 Apr 2008) | 4 lines

forward-porting merge_bib_records perm and adding it to the seed data

........

r9453 | miker | 2008-04-26 12:05:14 -0400 (Sat, 26 Apr 2008) | 1 line

sql script for upgrading to a materialized version of the simple record view (reporting speed)

........

r9456 | miker | 2008-04-26 12:13:13 -0400 (Sat, 26 Apr 2008) | 1 line

add reporter.materialized_simple_record to the base schema

........

r9458 | miker | 2008-04-26 12:49:25 -0400 (Sat, 26 Apr 2008) | 1 line

base materialized simple record on old super_simple_record

........

r9460 | miker | 2008-04-26 14:05:58 -0400 (Sat, 26 Apr 2008) | 1 line

allow deleted records to be visible

........

r9461 | dbs | 2008-04-27 21:11:04 -0400 (Sun, 27 Apr 2008) | 2 lines

Kill one JavaScript? warning for an apparent typo

........

r9462 | dbs | 2008-04-27 21:25:36 -0400 (Sun, 27 Apr 2008) | 2 lines

Clean up trailing comma

........

r9464 | dbs | 2008-04-27 21:34:29 -0400 (Sun, 27 Apr 2008) | 2 lines

ECMA-262 isn't happy when variables are declared without var...

........

r9465 | dbs | 2008-04-27 22:40:20 -0400 (Sun, 27 Apr 2008) | 2 lines

Silence a few more trailing comma warnings

........

r9466 | dbs | 2008-04-27 22:41:50 -0400 (Sun, 27 Apr 2008) | 2 lines

And silence a few more trailing comma warnings

........

r9467 | dbs | 2008-04-27 22:57:52 -0400 (Sun, 27 Apr 2008) | 2 lines

Silence some more trailing comma warnings

........

r9468 | dbs | 2008-04-27 23:00:50 -0400 (Sun, 27 Apr 2008) | 2 lines

Silence another trailing comma warning

........

r9469 | dbs | 2008-04-27 23:13:43 -0400 (Sun, 27 Apr 2008) | 2 lines

ue.xhtml goes nuts with " if we escape the calendar script

........

r9470 | dbs | 2008-04-27 23:38:54 -0400 (Sun, 27 Apr 2008) | 2 lines

Declare the globalOrgTypes var to silence another warning

........

r9471 | dbs | 2008-04-27 23:43:59 -0400 (Sun, 27 Apr 2008) | 2 lines

Silence a few more trailing comma warnings

........

r9472 | dbs | 2008-04-27 23:52:44 -0400 (Sun, 27 Apr 2008) | 2 lines

Silence a lot more trailing comma warnings

........

r9473 | miker | 2008-04-28 01:08:58 -0400 (Mon, 28 Apr 2008) | 1 line

made translation widget self-contained

........

r9474 | miker | 2008-04-28 03:42:25 -0400 (Mon, 28 Apr 2008) | 1 line

fixing translator for grids

........

r9476 | miker | 2008-04-28 08:15:25 -0400 (Mon, 28 Apr 2008) | 1 line

improved concurrency detection

........

r9477 | dbs | 2008-04-28 11:24:37 -0400 (Mon, 28 Apr 2008) | 2 lines

x.y.z for egg versions seems to satisfy setuptools and avoid first-time install errors

........

r9479 | miker | 2008-04-28 14:19:37 -0400 (Mon, 28 Apr 2008) | 1 line

1.2.1 to 1.2.2 upgrade SQL script; will get 1.2 to 1.4/2.0 as well

........

r9481 | dbs | 2008-04-29 09:48:20 -0400 (Tue, 29 Apr 2008) | 2 lines

Try giving our application a default locale

........

r9482 | dbs | 2008-04-29 09:49:05 -0400 (Tue, 29 Apr 2008) | 2 lines

Remove duplicate span id

........

r9483 | dbs | 2008-04-29 09:49:47 -0400 (Tue, 29 Apr 2008) | 2 lines

JavaScript? strictness cleanup to reduce warning noise at console

........

r9484 | dbs | 2008-04-29 10:25:52 -0400 (Tue, 29 Apr 2008) | 2 lines

Silence more trailing comma warnings

........

r9486 | miker | 2008-04-29 13:50:09 -0400 (Tue, 29 Apr 2008) | 1 line

protecting against existing views

........

r9490 | miker | 2008-04-30 00:50:12 -0400 (Wed, 30 Apr 2008) | 1 line

adding some more billing report views

........

r9492 | miker | 2008-04-30 08:17:45 -0400 (Wed, 30 Apr 2008) | 1 line

protect against empty folder names

........

r9494 | miker | 2008-04-30 08:30:18 -0400 (Wed, 30 Apr 2008) | 1 line

bz#618, stop duplicate authority cross-ref suggestions

........

r9496 | miker | 2008-04-30 18:19:36 -0400 (Wed, 30 Apr 2008) | 1 line

use org_unit datatype for easier filtering

........

r9498 | miker | 2008-04-30 22:47:09 -0400 (Wed, 30 Apr 2008) | 1 line

typo in field name

........

r9505 | erickson | 2008-05-03 07:58:45 -0400 (Sat, 03 May 2008) | 1 line

added additional needed script path to sip config

........

r9506 | miker | 2008-05-04 19:51:08 -0400 (Sun, 04 May 2008) | 1 line

break up the transactions a bit more, and move DROPs outside of transactions, as they are allowed to fail

........

Location:

branches/acq-experiment

Files:

• . (modified) (1 prop)
• Open-ILS/src/sql/Pg/reporter-schema.sql (modified) (2 diffs)

branches/acq-experiment/Open-ILS/src/sql/Pg/reporter-schema.sql

@@ -130 +130 @@
         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;
 
-CREATE OR REPLACE VIEW reporter.super_simple_record AS
+CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
 SELECT  r.id,
         r.fingerprint,
@@ -152 +151 @@
         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 TABLE reporter.materialized_simple_record AS SELECT * FROM reporter.old_super_simple_record WHERE 1=0;
+ALTER TABLE reporter.materialized_simple_record ADD PRIMARY KEY (id);
+CREATE VIEW reporter.super_simple_record AS SELECT * FROM reporter.materialized_simple_record;
+
+CREATE OR REPLACE FUNCTION reporter.simple_rec_sync () RETURNS TRIGGER AS $$
+DECLARE
+    r_id        BIGINT;
+    new_data    RECORD;
+BEGIN
+    IF TG_OP IN ('DELETE') THEN
+        r_id := OLD.record;
+    ELSE
+        r_id := NEW.record;
+    END IF;
+
+    SELECT * INTO new_data FROM reporter.materialized_simple_record WHERE id = r_id FOR UPDATE;
+    DELETE FROM reporter.materialized_simple_record WHERE id = r_id;
+
+    IF TG_OP IN ('DELETE') THEN
+        RETURN OLD;
+    ELSE
+        INSERT INTO reporter.materialized_simple_record SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record WHERE id = NEW.record;
+        RETURN NEW;
+    END IF;
+
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE TRIGGER zzz_update_materialized_simple_record_tgr
+    AFTER INSERT OR UPDATE OR DELETE ON metabib.full_rec
+    FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_sync();
 
 CREATE OR REPLACE VIEW reporter.demographic AS