From c9f6a9688d67af57eca534a3bce41aaca05b4f7d Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Sun, 22 May 2011 01:00:42 -0400 Subject: [PATCH] Add staging schema and authority indexes to upgrade script Post-2.0.6 goodness that we want to have running if we're going to be running this thing all night. Signed-off-by: Dan Scott --- src/sql/Pg/1.6.1-2.0-upgrade-db.sql | 68 +++++++++++++++++++++++++++++++++++++ 1 file changed, 68 insertions(+) diff --git a/src/sql/Pg/1.6.1-2.0-upgrade-db.sql b/src/sql/Pg/1.6.1-2.0-upgrade-db.sql index 7ba8ff80e2..7be4d90ac6 100644 --- a/src/sql/Pg/1.6.1-2.0-upgrade-db.sql +++ b/src/sql/Pg/1.6.1-2.0-upgrade-db.sql @@ -18793,6 +18793,7 @@ ALTER TABLE acq.provider_contact ALTER TABLE actor.stat_cat ADD COLUMN usr_summary BOOL NOT NULL DEFAULT FALSE; + -- Recreate some foreign keys that were somehow dropped, probably -- by some kind of cascade from an inherited table: @@ -19057,6 +19058,69 @@ CREATE TRIGGER autogenerate_placeholder_barcode COMMIT; +BEGIN; +-- stick loading the staging schema into a separate transaction, as +-- libraries upgrading from earlier stock versions of Evergreen won't have +-- it, but at least one library is known to have it in a pre-2.0 variant +-- setup. +CREATE SCHEMA staging; + +CREATE TABLE staging.user_stage ( + row_id BIGSERIAL PRIMARY KEY, + row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + usrname TEXT NOT NULL, + profile TEXT, + email TEXT, + passwd TEXT, + ident_type INT DEFAULT 3, + first_given_name TEXT, + second_given_name TEXT, + family_name TEXT, + day_phone TEXT, + evening_phone TEXT, + home_ou INT DEFAULT 2, + dob TEXT, + complete BOOL DEFAULT FALSE +); + +CREATE TABLE staging.card_stage ( -- for new library barcodes + row_id BIGSERIAL PRIMARY KEY, + row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + usrname TEXT NOT NULL, + barcode TEXT NOT NULL, + complete BOOL DEFAULT FALSE +); + +CREATE TABLE staging.mailing_address_stage ( + row_id BIGSERIAL PRIMARY KEY, + row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + usrname TEXT NOT NULL, -- user's SIS barcode, for linking + street1 TEXT, + street2 TEXT, + city TEXT NOT NULL DEFAULT '', + state TEXT NOT NULL DEFAULT 'OK', + country TEXT NOT NULL DEFAULT 'US', + post_code TEXT NOT NULL, + complete BOOL DEFAULT FALSE +); + +CREATE TABLE staging.billing_address_stage ( + LIKE staging.mailing_address_stage INCLUDING DEFAULTS +); + +ALTER TABLE staging.billing_address_stage ADD PRIMARY KEY (row_id); + +CREATE TABLE staging.statcat_stage ( + row_id BIGSERIAL PRIMARY KEY, + row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + usrname TEXT NOT NULL, + statcat TEXT NOT NULL, -- for things like 'Year of study' + value TEXT NOT NULL, -- and the value, such as 'Freshman' + complete BOOL DEFAULT FALSE +); + +COMMIT; + -- Some operations go outside of the transaction, because they may -- legitimately fail. @@ -20181,6 +20245,10 @@ INSERT INTO config.org_unit_setting_type -- Speed up call number browsing CREATE INDEX asset_call_number_label_sortkey_browse ON asset.call_number(oils_text_as_bytea(label_sortkey), oils_text_as_bytea(label), id, owning_lib) WHERE deleted IS FALSE OR deleted = FALSE; +-- Speed up authority lookups +CREATE INDEX authority_record_deleted_idx ON authority.record_entry(id) WHERE deleted IS FALSE OR deleted = false; +CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec(value) WHERE subfield = 'a'; + \qecho Upgrade script completed. \qecho But wait, there's more: please run reingest-1.6-2.0.pl \qecho in order to create an SQL script to run to partially reindex -- 2.11.0