From 1870da2b6c18dde63f0b927e12ebef5a080cfd3f Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Tue, 9 Mar 2021 16:36:14 -0500 Subject: [PATCH] LP#1902939: Student Card SQL/IDL changes --- Open-ILS/examples/fm_IDL.xml | 81 ++++++++++++++++++---- Open-ILS/src/sql/Pg/600.schema.student_card.sql | 16 +++-- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 4 +- .../sql/Pg/upgrade/XXXX.schema.student_cards.sql | 52 ++++++++++++++ 4 files changed, 132 insertions(+), 21 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.student_cards.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 105605d4a2..24f36a072d 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -14033,26 +14033,57 @@ SELECT usr, - - - - - + + + + + + + + + + - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -14099,6 +14130,28 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/600.schema.student_card.sql b/Open-ILS/src/sql/Pg/600.schema.student_card.sql index 73f93bedd8..07d81033b8 100644 --- a/Open-ILS/src/sql/Pg/600.schema.student_card.sql +++ b/Open-ILS/src/sql/Pg/600.schema.student_card.sql @@ -1,26 +1,30 @@ BEGIN; +DROP SCHEMA student_card CASCADE; + CREATE SCHEMA student_card; CREATE TABLE student_card.district ( id SERIAL PRIMARY KEY, code TEXT NOT NULL UNIQUE, name TEXT NOT NULL UNIQUE, + active BOOLEAN NOT NULL DEFAULT TRUE, state_id INTEGER NOT NULL UNIQUE, contact_name TEXT, contact_email TEXT, - remote_host TEXT NOT NULL, - remote_user TEXT, - remote_pass VARCHAR(255), --TODO: encrypt this data with PGP? - remote_remote_dir TEXT, - exceptions_dir TEXT + ftp_host TEXT NOT NULL, + ftp_port INTEGER, + ftp_user TEXT, + ftp_pass VARCHAR(255), --TODO: encrypt this data with PGP + ftp_remote_dir TEXT, + ftp_exceptions_dir TEXT ); CREATE TABLE student_card.school ( id SERIAL PRIMARY KEY, district_id INTEGER NOT NULL REFERENCES student_card.district (id), name TEXT NOT NULL UNIQUE, - state_id INTEGER NOT NULL, + state_id INTEGER NOT NULL, -- school ID per the State of Georgia grades TEXT, addr_street_1 TEXT, addr_street_2 TEXT, diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 25c06f8658..1d1acbbc5a 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -1964,7 +1964,9 @@ INSERT INTO permission.perm_list ( id, code, description ) VALUES ( 634, 'UPDATE_RECORD_NOTE', oils_i18n_gettext(634, 'Allow the user to update a record note', 'ppl', 'description')), ( 635, 'DELETE_RECORD_NOTE', oils_i18n_gettext(635, - 'Allow the user to delete a record note', 'ppl', 'description')) + 'Allow the user to delete a record note', 'ppl', 'description')), + ( 636, 'ADMIN_STUDENT_CARDS', oils_i18n_gettext(636, + 'Modify student card settings', 'ppl', 'description')) ; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.student_cards.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.student_cards.sql new file mode 100644 index 0000000000..d59d07223a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.student_cards.sql @@ -0,0 +1,52 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE SCHEMA student_card; + +CREATE TABLE student_card.district ( + id SERIAL PRIMARY KEY, + code TEXT NOT NULL UNIQUE, + name TEXT NOT NULL UNIQUE, + active BOOLEAN NOT NULL DEFAULT TRUE, + state_id INTEGER NOT NULL UNIQUE, + contact_name TEXT, + contact_email TEXT, + ftp_host TEXT NOT NULL, + ftp_port INTEGER, + ftp_user TEXT, + ftp_pass VARCHAR(255), --TODO: encrypt this data with PGP + ftp_remote_dir TEXT, + ftp_exceptions_dir TEXT +); + +CREATE TABLE student_card.school ( + id SERIAL PRIMARY KEY, + district_id INTEGER NOT NULL REFERENCES student_card.district (id), + name TEXT NOT NULL UNIQUE, + state_id INTEGER NOT NULL, -- school ID per the State of Georgia + grades TEXT, + addr_street_1 TEXT, + addr_street_2 TEXT, + addr_city TEXT, + addr_county TEXT, + addr_state TEXT, + addr_post_code TEXT, + eg_perm_group INTEGER NOT NULL REFERENCES permission.grp_tree (id), + home_ou INTEGER NOT NULL REFERENCES actor.org_unit (id) +); +CREATE UNIQUE INDEX student_card_school_state_id_idx ON student_card.school (district_id, state_id); + +CREATE TABLE student_card.import ( + id SERIAL PRIMARY KEY, + import_time TIMESTAMPTZ NOT NULL DEFAULT NOW(), + district_id INTEGER NOT NULL REFERENCES student_card.district (id), + filename TEXT, + error_message TEXT +); + +INSERT INTO permission.perm_list ( id, code, description ) VALUES + ( 628, 'ADMIN_STUDENT_CARDS', oils_i18n_gettext(628, + 'Modify student card settings', 'ppl', 'description')); + +COMMIT; -- 2.11.0