From 87887b4b17d427ec73c3f8218658cff1bcac7647 Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 9 Jan 2008 19:33:32 +0000 Subject: [PATCH] adding picklist related model schema and IDL data git-svn-id: svn://svn.open-ils.org/ILS/branches/acq-experiment@8367 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 128 ++++++++++++++++++++++++++++++++ Open-ILS/src/sql/Pg/200.schema.acq.sql | 131 +++++++++++++++++++++++++++------ 2 files changed, 238 insertions(+), 21 deletions(-) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 22acb57436..078cae2a5c 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -2476,6 +2476,134 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index da2663b7c4..bdae085730 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -76,34 +76,123 @@ CREATE TABLE acq.picklist ( owner INT NOT NULL REFERENCES actor.usr (id), name TEXT NOT NULL, create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), CONSTRAINT name_once_per_owner UNIQUE (name,owner) ); CREATE TABLE acq.picklist_entry ( - id SERIAL PRIMARY KEY, - picklist INT NOT NULL REFERENCES acq.picklist (id), - marc TEXT NOT NULL, - marc_title TEXT, - marc_author TEXT, - marc_lanuage TEXT, - marc_pagination TEXT, - marc_isbn TEXT, - marc_issn TEXT, - marc_identifier TEXT, - marc_publisher TEXT, - marc_pubdate TEXT, - marc_edition TEXT, - marc_price TEXT, - marc_currency TEXT REFERENCES acq.currency_type (code), + id BIGSERIAL PRIMARY KEY, + picklist INT NOT NULL REFERENCES acq.picklist (id), + provider INT REFERENCES acq.provider (id), + create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + marc TEXT NOT NULL, eg_bib_id INT, - source_label TEXT, - vendor_price TEXT, - vendor_currency TEXT REFERENCES acq.currency_type (code), - vendor_avail INT, - vendor_po TEXT, - vendor_identifier TEXT + source_label TEXT +); + +CREATE TABLE acq.picklist_entry_attr ( + id BIGSERIAL PRIMARY KEY, + picklist_entry BIGINT NOT NULL REFERENCES acq.picklist_entry (id), + attr_type TEXT NOT NULL, + attr_name TEXT NOT NULL, + attr_value TEXT NOT NULL ); +CREATE OR REPLACE FUNCTION public.extract_marc_field ( TEXT, BIGINT, TEXT ) RETURNS TEXT AS $$ + SELECT array_to_string( array_accum( output ),' ' ) FROM xpath_table('id', 'marc', $1, $3, 'id='||$2)x(id INT, output TEXT); +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$ + SELECT public.extract_marc_field('acq.picklist_entry', $1, $2); +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$ + SELECT public.extract_marc_field('biblio.record_entry', $1, $2); +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$ + SELECT public.extract_marc_field('authority.record_entry', $1, $2); +$$ LANGUAGE SQL; + +CREATE TABLE acq.picklist_attr_definition ( + id BIGSERIAL PRIMARY KEY, + code TEXT NOT NULL, + description TEXT NOT NULL, + xpath TEXT NOT NULL +); + +CREATE TABLE acq.picklist_marc_attr_definition ( + id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.picklist_attr_definition_id_seq') +) INHERITS (acq.picklist_attr_definition); + +CREATE TABLE acq.picklist_provider_attr_definition ( + id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.picklist_attr_definition_id_seq'), + provider INT NOT NULL REFERENCES acq.provider (id) +) INHERITS (acq.picklist_attr_definition); + +CREATE TABLE acq.picklist_generated_attr_definition ( + id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.picklist_attr_definition_id_seq') +) INHERITS (acq.picklist_attr_definition); + +CREATE TABLE acq.picklist_usr_attr_definition ( + id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.picklist_attr_definition_id_seq'), + usr INT NOT NULL REFERENCES actor.usr (id) +) INHERITS (acq.picklist_attr_definition); + +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('author','Author of work','//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('language','Lanuage of work','//*[@tag="240"]/*[@code="l"][1]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]'); +INSERT INTO acq.picklist_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]'); + +-- For example: +-- INSERT INTO acq.picklist_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]'); + +/* +Suggested vendor fields: + vendor_price + vendor_currency + vendor_avail + vendor_po + vendor_identifier +*/ + +CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$ +DECLARE + value TEXT; + atype TEXT; + prov INT; + adef RECORD; +BEGIN + FOR adef IN SELECT *,tableoid FROM acq.picklist_attr_definition LOOP + + SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid; + IF (atype = 'picklist_provider_attr_definition') THEN + SELECT provider INTO prov FROM acq.picklist_provider_attr_definition WHERE id = adef.id; + CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider; + END IF; + + SELECT extract_acq_marc_field(id, adef.xpath) INTO value FROM acq.picklist_entry WHERE id = NEW.id; + IF (value IS NOT NULL AND value <> '') THEN + INSERT INTO acq.picklist_entry_attr (picklist_entry, attr_type, attr_name, attr_value) VALUES (NEW.id, atype, adef.code, value); + END IF; + END LOOP; + + RETURN NULL; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER ingest_picklist_entry_trigger + AFTER INSERT OR UPDATE ON acq.picklist_entry + FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc(); + CREATE TABLE acq.budget ( id SERIAL PRIMARY KEY, org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE, -- 2.11.0