From f8c97c64c156750ee9e8a2c5287b401c6a6bf04d Mon Sep 17 00:00:00 2001 From: miker Date: Thu, 16 Sep 2010 00:40:50 +0000 Subject: [PATCH] Patch from Dan Wells: I will start with the changes which I think are straightforward. First, textual_holdings are sometimes used to augment the generated_coverage and at other times should completely replace it (generally when the holdings statement is too complex to be easily generated or has more detail than we care about). The 'show_generated' flag in each summary table is a simple means of indicating which type of display we want. Second, 'summary_method' will be consulted when generating the 'generated_coverage' fields, and tells us how any attached MFHD records (SREs) should be treated for this distribution in relation to the new structured data (attempt to merge the data, generate display data from both separately, or generate based on one or the other only). The changes to serial.caption_and_pattern are not as straightforward, but I think they are justifiable. Any given caption/pattern is only valid for a certain period of time (i.e. until it changes). As it stands, we can infer the start and end dates of a caption/pattern only by consulting the attached issuances. In practice this means retrieving and sorting sometimes hundreds of issuances to provide for the sorted display of just a few caption/patterns. As I work with these objects more, I am simply often wishing for a more convenient access point to this important data. This also means that the 'active' flag is redundant (caption/patterns with end_date-s are not active), but we need to get end_date in place before we can start removing the 'active' flag from the code. git-svn-id: svn://svn.open-ils.org/ILS/trunk@17715 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/210.schema.serials.sql | 16 ++++++++-- .../sql/Pg/upgrade/0403.schema.serials-tweaks.sql | 35 ++++++++++++++++++++++ 3 files changed, 49 insertions(+), 4 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0403.schema.serials-tweaks.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index c8c79a46f..41f379e14 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0402'); -- dbs +INSERT INTO config.upgrade_log (version) VALUES ('0403'); -- dbwells via miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/210.schema.serials.sql b/Open-ILS/src/sql/Pg/210.schema.serials.sql index a96a67c1e..a7fda127b 100644 --- a/Open-ILS/src/sql/Pg/210.schema.serials.sql +++ b/Open-ILS/src/sql/Pg/210.schema.serials.sql @@ -68,6 +68,8 @@ CREATE TABLE serial.caption_and_pattern ( CONSTRAINT cap_type CHECK ( type in ( 'basic', 'supplement', 'index' )), create_date TIMESTAMPTZ NOT NULL DEFAULT now(), + start_date TIMESTAMPTZ NOT NULL DEFAULT now(), + end_date TIMESTAMPTZ, active BOOL NOT NULL DEFAULT FALSE, pattern_code TEXT NOT NULL, -- must contain JSON enum_1 TEXT, @@ -90,6 +92,11 @@ CREATE TABLE serial.distribution ( record_entry BIGINT REFERENCES serial.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + summary_method TEXT CONSTRAINT sdist_summary_method_check + CHECK (summary_method IS NULL + OR summary_method IN ( 'add_to_sre', + 'merge_with_sre', 'use_sre_only', + 'use_sdist_only')), subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE @@ -274,7 +281,8 @@ CREATE TABLE serial.basic_summary ( ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, generated_coverage TEXT NOT NULL, - textual_holdings TEXT + textual_holdings TEXT, + show_generated BOOL NOT NULL DEFAULT TRUE ); CREATE INDEX serial_basic_summary_dist_idx ON serial.basic_summary (distribution); @@ -285,7 +293,8 @@ CREATE TABLE serial.supplement_summary ( ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, generated_coverage TEXT NOT NULL, - textual_holdings TEXT + textual_holdings TEXT, + show_generated BOOL NOT NULL DEFAULT TRUE ); CREATE INDEX serial_supplement_summary_dist_idx ON serial.supplement_summary (distribution); @@ -296,7 +305,8 @@ CREATE TABLE serial.index_summary ( ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, generated_coverage TEXT NOT NULL, - textual_holdings TEXT + textual_holdings TEXT, + show_generated BOOL NOT NULL DEFAULT TRUE ); CREATE INDEX serial_index_summary_dist_idx ON serial.index_summary (distribution); diff --git a/Open-ILS/src/sql/Pg/upgrade/0403.schema.serials-tweaks.sql b/Open-ILS/src/sql/Pg/upgrade/0403.schema.serials-tweaks.sql new file mode 100644 index 000000000..703932a3f --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0403.schema.serials-tweaks.sql @@ -0,0 +1,35 @@ +-- serials schema tweaks + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0403'); -- dbwells via miker + +------- caption_and_pattern changes +ALTER TABLE serial.caption_and_pattern +ADD COLUMN start_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(); + +ALTER TABLE serial.caption_and_pattern +ADD COLUMN end_date TIMESTAMP WITH TIME ZONE; + + +------- *_summary changes +ALTER TABLE serial.basic_summary +ADD COLUMN show_generated BOOL NOT NULL DEFAULT TRUE; + +ALTER TABLE serial.supplement_summary +ADD COLUMN show_generated BOOL NOT NULL DEFAULT TRUE; + +ALTER TABLE serial.index_summary +ADD COLUMN show_generated BOOL NOT NULL DEFAULT TRUE; + + +------- distribution changes +ALTER TABLE serial.distribution + +ADD COLUMN summary_method TEXT CONSTRAINT summary_method_check CHECK ( + summary_method IS NULL + OR summary_method IN ( 'add_to_sre', + 'merge_with_sre', 'use_sre_only', + 'use_sdist_only')); + +COMMIT; -- 2.11.0