From 2dfa521c1f5a3bc086da41d63d4938524ab3b7c3 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 16 Sep 2015 11:15:28 -0400 Subject: [PATCH] JBAS-886 Student groups and policies SQL Schools -- Student Ecards -- Classroom Cards Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/student-groups.sql | 67 +++++++++++++++++++++++++++++++ KCLS/sql/schema/revert/student-groups.sql | 42 +++++++++++++++++++ KCLS/sql/schema/sqitch.plan | 1 + KCLS/sql/schema/verify/student-groups.sql | 7 ++++ 4 files changed, 117 insertions(+) create mode 100644 KCLS/sql/schema/deploy/student-groups.sql create mode 100644 KCLS/sql/schema/revert/student-groups.sql create mode 100644 KCLS/sql/schema/verify/student-groups.sql diff --git a/KCLS/sql/schema/deploy/student-groups.sql b/KCLS/sql/schema/deploy/student-groups.sql new file mode 100644 index 0000000000..05d2de34da --- /dev/null +++ b/KCLS/sql/schema/deploy/student-groups.sql @@ -0,0 +1,67 @@ +-- Deploy kcls-evergreen:student-groups to pg +-- requires: blanket-po + +BEGIN; + +DO $$ +BEGIN + IF evergreen.insert_on_deploy() THEN + +INSERT INTO permission.perm_list (code, description) VALUES + ( 'group_application.user.students', + 'Allows staff to create and modify student accounts'), + ( 'ACCESS_EBOOKS_AND_DATABASES', + 'Allows patrons to access digital materials'); + +INSERT INTO permission.grp_tree + (id, name, parent, usergroup, perm_interval, description, application_perm) +VALUES ( + 900, 'Schools', 1, FALSE, '2 years', + 'Parent group of all student groups', + 'group_application.user.students' +); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES + (2, (SELECT id FROM permission.perm_list + WHERE code = 'ACCESS_EBOOKS_AND_DATABASES'), 0, FALSE), + (3, (SELECT id FROM permission.perm_list + WHERE code = 'ACCESS_EBOOKS_AND_DATABASES'), 0, FALSE), + (900, (SELECT id FROM permission.perm_list + WHERE code = 'ACCESS_EBOOKS_AND_DATABASES'), 0, FALSE); + +INSERT INTO permission.grp_tree + (id, name, parent, usergroup, perm_interval) VALUES + (901, 'Student Ecard', 900, TRUE, '2 years'), + (902, 'Classroom Databases', 900, TRUE, '2 years'); + +PERFORM SETVAL('permission.grp_tree_id_seq', 1000); + +INSERT INTO config.circ_matrix_matchpoint + (active, org_unit, grp, circulate) VALUES ( TRUE, 1, 900, FALSE); + +INSERT INTO config.hold_matrix_matchpoint ( + active, usr_grp, requestor_grp, ref_flag, holdable, + distance_is_from_owner, include_frozen_holds, stop_blocked_user, + strict_ou_match +) VALUES (TRUE, 900, 1, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE); + + +-- modify OPAC_LOGIN perm so that's not applied to Users, +-- but certain sub-groups. +DELETE FROM permission.grp_perm_map WHERE perm = 2 AND grp = 1; +INSERT INTO permission.grp_perm_map (perm, grp, depth) VALUES + (2, 2, 0), -- Patrons + (2, 3, 0), -- Staff + (2, 888, 0); -- SIP + +-- new ident type for students +INSERT INTO config.identification_type (id, name) + VALUES (101, 'Sch-district file'); + +PERFORM SETVAL('config.identification_type_id_seq', 101); + + END IF; -- insert_on_deploy +END $$; + +COMMIT; + diff --git a/KCLS/sql/schema/revert/student-groups.sql b/KCLS/sql/schema/revert/student-groups.sql new file mode 100644 index 0000000000..afc4eafb42 --- /dev/null +++ b/KCLS/sql/schema/revert/student-groups.sql @@ -0,0 +1,42 @@ +-- Revert kcls-evergreen:student-groups from pg + +BEGIN; + +DO $$ +BEGIN + IF evergreen.insert_on_deploy() THEN + +DELETE FROM config.hold_matrix_matchpoint WHERE usr_grp = 900; + +DELETE FROM config.circ_matrix_matchpoint WHERE grp = 900; + +DELETE FROM permission.grp_perm_map WHERE perm = + (SELECT id FROM permission.perm_list + WHERE code = 'ACCESS_EBOOKS_AND_DATABASES'); + +DELETE FROM permission.grp_tree WHERE parent = 900; + +DELETE FROM permission.grp_tree WHERE id = 900; + +PERFORM SETVAL('permission.grp_tree_id_seq', 1000); + +DELETE FROM permission.perm_list WHERE code IN ( + 'group_application.user.students', + 'ACCESS_EBOOKS_AND_DATABASES' +); + +DELETE FROM permission.grp_perm_map WHERE + (perm = 2 AND grp = 2) OR + (perm = 2 AND grp = 3) OR + (perm = 2 AND grp = 888); + +INSERT INTO permission.grp_perm_map (perm, grp, depth) VALUES (2, 1, 0); + +DELETE FROM config.identification_type WHERE id = 101; + +PERFORM SETVAL('config.identification_type_id_seq', 100); + + END IF; -- insert-on-deploy +END $$; + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index a63a4c3980..127d754490 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -12,3 +12,4 @@ insert-on-deploy [KMAIN-1765-reingest-causing-false-browse-headings] 2015-08-14T copy-stat-checkout-ok [insert-on-deploy] 2015-08-14T20:47:19Z Bill Erickson # copy_status.checkout_ok flag for Grand Opening blanket-po [copy-stat-checkout-ok] 2015-08-14T21:03:02Z Bill Erickson # Blanket PO collection-hq-floating-case [blanket-po] 2015-09-17T18:42:09Z Bill Erickson # CollectionHQ floating CASE/WHEN fix +student-groups [blanket-po] 2015-09-16T14:36:00Z Bill Erickson # New groups for student cards diff --git a/KCLS/sql/schema/verify/student-groups.sql b/KCLS/sql/schema/verify/student-groups.sql new file mode 100644 index 0000000000..d6316508db --- /dev/null +++ b/KCLS/sql/schema/verify/student-groups.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:student-groups on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0