From 66b978a66d4779199b77d8d31809ccc085004c01 Mon Sep 17 00:00:00 2001 From: erickson Date: Fri, 15 Jan 2010 15:57:49 +0000 Subject: [PATCH] Patch from Joe Atzberger, with some IDL additions, to add a new generic remote_account table for managing ftp/scp/etc logins and a subclass table specific to ACQ providers, initially for EDI retrieval and delivery. git-svn-id: svn://svn.open-ils.org/ILS/trunk@15322 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 62 ++++++++++++++++++++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 13 +++++ Open-ILS/src/sql/Pg/200.schema.acq.sql | 9 ++++ Open-ILS/src/sql/Pg/800.fkeys.sql | 4 ++ .../0133.schema.config_accounts_and_acq_edi.sql | 28 ++++++++++ 5 files changed, 116 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0133.schema.config_accounts_and_acq_edi.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 9944aba88f..7b793c4757 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -527,6 +527,31 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + @@ -4311,11 +4336,13 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + @@ -5022,6 +5049,41 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 2614d06042..bf72277c28 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -449,6 +449,19 @@ COMMENT ON TABLE config.net_access_level IS $$ */ $$; + +CREATE TABLE config.remote_account ( + id SERIAL PRIMARY KEY, + label TEXT NOT NULL, + host TEXT NOT NULL, -- name or IP, :port optional + username TEXT, -- optional, since we could default to $USER + password TEXT, -- optional, since we could use SSH keys, or anonymous login. + account TEXT, -- aka profile or FTP "account" command + path TEXT, -- aka directory + owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + last_activity TIMESTAMP WITH TIME ZONE +); + CREATE TABLE config.audience_map ( code TEXT PRIMARY KEY, value TEXT NOT NULL, diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index ed5029db62..9b7f0f5345 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -37,6 +37,7 @@ CREATE TABLE acq.provider ( code TEXT NOT NULL, holding_tag TEXT, san TEXT, + edi_default INT, -- REFERENCES acq.edi_account (id) DEFERRABLE INITIALLY DEFERRED CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner), CONSTRAINT code_once_per_owner UNIQUE (code, owner) ); @@ -559,6 +560,14 @@ CREATE TABLE acq.fiscal_year ( CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin ) ); +CREATE TABLE acq.edi_account ( -- similar tables can extend remote_account for other parts of EG + provider INT NOT NULL REFERENCES acq.provider (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + in_dir TEXT -- incoming messages dir (probably different than config.remote_account.path, the outgoing dir) +) INHERITS (config.remote_account); + +-- We need a UNIQUE constraint here also, to support the FK from acq.provider.edi_default +ALTER TABLE acq.edi_account ADD CONSTRAINT acq_edi_account_id_unique UNIQUE (id); + -- Functions CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text); diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index b273fd45eb..0dcee31058 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -37,6 +37,8 @@ ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_billing_address_fkey FO ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_holds_address_fkey FOREIGN KEY (holds_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_ill_address_fkey FOREIGN KEY (ill_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE acq.provider ADD CONSTRAINT acq_provider_edi_default_fkey FOREIGN KEY (edi_default) REFERENCES acq.edi_account (id) DEFERRABLE INITIALLY DEFERRED; + ALTER TABLE biblio.record_note ADD CONSTRAINT biblio_record_note_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE biblio.record_note ADD CONSTRAINT biblio_record_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE biblio.record_note ADD CONSTRAINT biblio_record_note_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; @@ -97,6 +99,8 @@ ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_target_copy_fke ALTER TABLE config.billing_type ADD CONSTRAINT config_billing_type_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE config.remote_account ADD CONSTRAINT config_remote_account_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + ALTER TABLE config.org_unit_setting_type ADD CONSTRAINT view_perm_fkey FOREIGN KEY (view_perm) REFERENCES permission.perm_list (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE config.org_unit_setting_type ADD CONSTRAINT update_perm_fkey FOREIGN KEY (update_perm) REFERENCES permission.perm_list (id) DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/upgrade/0133.schema.config_accounts_and_acq_edi.sql b/Open-ILS/src/sql/Pg/upgrade/0133.schema.config_accounts_and_acq_edi.sql new file mode 100644 index 0000000000..5d0ebdbcdd --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0133.schema.config_accounts_and_acq_edi.sql @@ -0,0 +1,28 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0133'); -- atz + +CREATE TABLE config.remote_account ( + id SERIAL PRIMARY KEY, + label TEXT NOT NULL, + host TEXT NOT NULL, -- name or IP, :port optional + username TEXT, -- optional, since we could default to $USER + password TEXT, -- optional, since we could use SSH keys, or anonymous login. + account TEXT, -- aka profile or FTP "account" command + path TEXT, -- aka directory + owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + last_activity TIMESTAMP WITH TIME ZONE +); + +CREATE TABLE acq.edi_account ( -- similar tables can extend remote_account for other parts of EG + provider INT NOT NULL REFERENCES acq.provider (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + in_dir TEXT -- incoming messages dir (probably different than config.remote_account.path, the outgoing dir) +) INHERITS (config.remote_account); + +-- We need a UNIQUE constraint here also, to support the FK in the next command +ALTER TABLE acq.edi_account ADD CONSTRAINT acq_edi_account_id_unique UNIQUE (id); + +-- null edi_default is OK... it has to be, since we have no values in acq.edi_account yet +ALTER TABLE acq.provider ADD COLUMN edi_default INT REFERENCES acq.edi_account (id) DEFERRABLE INITIALLY DEFERRED; + +COMMIT; -- 2.11.0