From 668ff9c426761758c5352bebac55883419eb4399 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Mon, 13 Aug 2018 21:20:49 -0400 Subject: [PATCH] LP#1772680: schema updates This includes tables used by the RBdigital integration, putting them in a new schema called 'extra' that's meant to collect Evergreen extensions and integrations that are optional but ship with Evergreen. Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/700.schema.extras.sql | 62 ++++++++++++++++++++++ Open-ILS/src/sql/Pg/sql_file_manifest | 2 + .../Pg/upgrade/YYYY.schema.extras_rbdigital.sql | 58 ++++++++++++++++++++ 3 files changed, 122 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/700.schema.extras.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/YYYY.schema.extras_rbdigital.sql diff --git a/Open-ILS/src/sql/Pg/700.schema.extras.sql b/Open-ILS/src/sql/Pg/700.schema.extras.sql new file mode 100644 index 0000000000..4ffc52c30d --- /dev/null +++ b/Open-ILS/src/sql/Pg/700.schema.extras.sql @@ -0,0 +1,62 @@ +-- Schema for storing database objects used by optional Evergreen +-- extensions and integrations that are included in the core +-- Evergreen distribution. + +DROP SCHEMA IF EXISTS extra CASCADE; + +CREATE SCHEMA extra; -- schema for various extensions + +CREATE TABLE extra.rbdigital_digital_services ( + id SERIAL PRIMARY KEY, + name CHARACTER VARYING(50) NOT NULL, + is_enabled BOOLEAN NOT NULL, + search_uri CHARACTER VARYING(120) NOT NULL, + vendor_key CHARACTER VARYING(20) NOT NULL, + display_order SMALLINT DEFAULT 1 +); +COMMENT ON TABLE extra.rbdigital_digital_services IS 'This table should contain a single record for each digital media service integrated into Evergreen. '; + +INSERT INTO extra.rbdigital_digital_services (id, name, is_enabled, search_uri, vendor_key, display_order) VALUES (1, 'Evergreen', true, '/results', 'default', 1); +INSERT INTO extra.rbdigital_digital_services (id, name, is_enabled, search_uri, vendor_key, display_order) VALUES (2, 'RBdigital', true, '/ebook_api/rbdigital/results', 'rbdigital', 2); + +CREATE TABLE extra.rbdigital_library ( + home_library_url CHARACTER VARYING(250), + created_timestamp TIMESTAMP(6) WITH TIME ZONE DEFAULT now(), + updated_timestamp TIMESTAMP(6) WITH TIME ZONE DEFAULT now(), + id SERIAL PRIMARY KEY, + home_ou INTEGER NOT NULL REFERENCES actor.org_unit(id) +); +COMMENT ON TABLE extra.rbdigital_library IS 'A table defining each entity that may purchase titles.'; + +CREATE TABLE extra.rbdigital_digital_services_authorized ( + digital_services_id SMALLSERIAL PRIMARY KEY, + digital_services_library_id CHARACTER VARYING(64), + digital_services_library_token CHARACTER VARYING(64), + digital_services_name CHARACTER VARYING(20), + digital_services_library_url CHARACTER VARYING(250), + id SERIAL NOT NULL, + library_id INTEGER REFERENCES extra.rbdigital_library(id), + home_ou INTEGER +); +COMMENT ON TABLE extra.rbdigital_digital_services_authorized IS 'A table listing the digital services authorized for each entity listed in the library table described above.'; + +CREATE TABLE extra.rbdigital_search_field_mappings ( + id BIGSERIAL PRIMARY KEY, + evergreen_field_value CHARACTER VARYING(120), + digital_services_field_value CHARACTER VARYING(120), + search_fields_id INTEGER, + evergreen_field_code CHARACTER VARYING(15), + digital_services_field_code CHARACTER VARYING(25) +); + +CREATE TABLE extra.rbdigital_search_fields ( + id SERIAL PRIMARY KEY, + digital_services_id INTEGER, + evergreen_field CHARACTER VARYING(125), + digital_services_field CHARACTER VARYING(125), + field_type CHARACTER VARYING(50) +); + +COMMENT ON TABLE extra.rbdigital_search_fields IS 'The purpose of this table is to map the Evergreen search fields to the analogous fields for each digital service. Therefore each integrated digital service will have a record in this table.'; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/sql_file_manifest b/Open-ILS/src/sql/Pg/sql_file_manifest index 97c92a0a35..7d89c892dc 100644 --- a/Open-ILS/src/sql/Pg/sql_file_manifest +++ b/Open-ILS/src/sql/Pg/sql_file_manifest @@ -48,6 +48,8 @@ FTS_CONFIG_FILE 500.view.cross-schema.sql +700.schema.extras.sql + 800.fkeys.sql 900.audit-functions.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.extras_rbdigital.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.extras_rbdigital.sql new file mode 100644 index 0000000000..076db26222 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.extras_rbdigital.sql @@ -0,0 +1,58 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE SCHEMA extra; -- schema for various extensions + +CREATE TABLE extra.rbdigital_digital_services ( + id SERIAL PRIMARY KEY, + name CHARACTER VARYING(50) NOT NULL, + is_enabled BOOLEAN NOT NULL, + search_uri CHARACTER VARYING(120) NOT NULL, + vendor_key CHARACTER VARYING(20) NOT NULL, + display_order SMALLINT DEFAULT 1 +); +COMMENT ON TABLE extra.rbdigital_digital_services IS 'This table should contain a single record for each digital media service integrated into Evergreen. '; + +INSERT INTO extra.rbdigital_digital_services (id, name, is_enabled, search_uri, vendor_key, display_order) VALUES (1, 'Evergreen', true, '/results', 'default', 1); +INSERT INTO extra.rbdigital_digital_services (id, name, is_enabled, search_uri, vendor_key, display_order) VALUES (2, 'RBdigital', true, '/ebook_api/rbdigital/results', 'rbdigital', 2); + +CREATE TABLE extra.rbdigital_library ( + home_library_url CHARACTER VARYING(250), + created_timestamp TIMESTAMP(6) WITH TIME ZONE DEFAULT now(), + updated_timestamp TIMESTAMP(6) WITH TIME ZONE DEFAULT now(), + id SERIAL PRIMARY KEY, + home_ou INTEGER NOT NULL REFERENCES actor.org_unit(id) +); +COMMENT ON TABLE extra.rbdigital_library IS 'A table defining each entity that may purchase titles.'; + +CREATE TABLE extra.rbdigital_digital_services_authorized ( + digital_services_id SMALLSERIAL PRIMARY KEY, + digital_services_library_id CHARACTER VARYING(64), + digital_services_library_token CHARACTER VARYING(64), + digital_services_name CHARACTER VARYING(20), + digital_services_library_url CHARACTER VARYING(250), + id SERIAL NOT NULL, + library_id INTEGER REFERENCES extra.rbdigital_library(id), + home_ou INTEGER +); +COMMENT ON TABLE extra.rbdigital_digital_services_authorized IS 'A table listing the digital services authorized for each entity listed in the library table described above.'; + +CREATE TABLE extra.rbdigital_search_field_mappings ( + id BIGSERIAL PRIMARY KEY, + evergreen_field_value CHARACTER VARYING(120), + digital_services_field_value CHARACTER VARYING(120), + search_fields_id INTEGER, + evergreen_field_code CHARACTER VARYING(15), + digital_services_field_code CHARACTER VARYING(25) +); + +CREATE TABLE extra.rbdigital_search_fields ( + id SERIAL PRIMARY KEY, + digital_services_id INTEGER, + evergreen_field CHARACTER VARYING(125), + digital_services_field CHARACTER VARYING(125), + field_type CHARACTER VARYING(50) +); + +COMMENT ON TABLE extra.rbdigital_search_fields IS 'The purpose of this table is to map the Evergreen search fields to the analogous fields for each digital service. Therefore each integrated digital service will have a record in this table.'; -- 2.11.0