From 9dc1d89f3401ba3b6047875ea2133d55818309f1 Mon Sep 17 00:00:00 2001 From: Dan Pearl Date: Fri, 9 Sep 2016 11:31:41 -0400 Subject: [PATCH] Schema for course reserves to be integrated into Evergreen. --- Open-ILS/examples/fm_IDL.xml | 170 +++++++++++++++ Open-ILS/src/sql/Pg/600.schema.reserves.sql | 300 +++++++++++++++++++++++++++ Open-ILS/src/sql/Pg/950.data.seed-values.sql | 4 +- 3 files changed, 473 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/600.schema.reserves.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 7126f16779..634dd8ef68 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -10860,6 +10860,176 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/600.schema.reserves.sql b/Open-ILS/src/sql/Pg/600.schema.reserves.sql new file mode 100644 index 0000000000..754b1c9d11 --- /dev/null +++ b/Open-ILS/src/sql/Pg/600.schema.reserves.sql @@ -0,0 +1,300 @@ +/* + * Copyright (C) 2016 C/W MARS Inc. + * Dan Pearl + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; either version 2 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + */ + +DROP SCHEMA IF EXISTS reserves CASCADE; + +BEGIN; +CREATE SCHEMA reserves; +COMMENT ON SCHEMA reserves IS $$ +Holds all tables pertaining to course reserves +$$; + +-- Function used by all reserves tables to update last_modified date when +-- the row is touched. +CREATE OR REPLACE FUNCTION reserves.update_last_modified_column() + RETURNS trigger AS $$ +BEGIN + NEW.last_modified = now(); + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +------------------------------------------------------------------------- +-- reserves.department +------------------------------------------------------------------------- +CREATE TABLE reserves.department ( + id SERIAL PRIMARY KEY, + created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + last_modified TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + org_unit INT NOT NULL REFERENCES actor.org_unit (id), + dept_name TEXT NOT NULL, + contact_name TEXT, + contact_phone TEXT, + contact_email TEXT, + deleted BOOL DEFAULT false NOT NULL +-- FUTURE: allow actor.id of contact instead of text for better db consistency +); + +CREATE UNIQUE INDEX res_dept_once_per_org ON reserves.department (org_unit, dept_name) WHERE deleted IS FALSE; + +CREATE RULE protect_department_delete AS + ON DELETE TO reserves.department DO INSTEAD + UPDATE reserves.department SET deleted = TRUE WHERE OLD.id = reserves.department.id; + + +COMMENT ON TABLE reserves.department IS $$ +Department + +This table holds department-level information ("Chemistry", "Literature") +and contact information. +$$; +COMMENT ON COLUMN reserves.department.id IS 'system generated'; +COMMENT ON COLUMN reserves.department.created IS 'system generated'; +COMMENT ON COLUMN reserves.department.last_modified IS 'system generated'; + +CREATE TRIGGER update_reserves_department_modtime + BEFORE UPDATE ON reserves.department FOR EACH ROW + EXECUTE PROCEDURE reserves.update_last_modified_column(); + +------------------------------------------------------------------------- +-- reserves.term +------------------------------------------------------------------------- +CREATE TABLE reserves.term ( + id SERIAL PRIMARY KEY, + created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + last_modified DATE DEFAULT now() NOT NULL, + org_unit INT NOT NULL REFERENCES actor.org_unit (id), + term_code TEXT NOT NULL, + term_name TEXT NOT NULL, + start DATE NOT NULL, + finish DATE, + deleted BOOL DEFAULT FALSE NOT NULL +-- Constraints needed to prevent duplication of term_code or term_name within +-- an org unit. + CHECK (finish IS NULL OR start <= finish) +); +CREATE RULE protect_term_delete AS + ON DELETE TO reserves.term DO INSTEAD + UPDATE reserves.term SET deleted = TRUE WHERE OLD.id = reserves.term.id; + +CREATE UNIQUE INDEX res_term_name_once_per_org ON reserves.term (org_unit, term_name) WHERE deleted IS FALSE; +CREATE UNIQUE INDEX res_term_code_once_per_org ON reserves.term (org_unit, term_code) WHERE deleted IS FALSE; +CREATE INDEX res_term_org_unit_idx ON reserves.term (org_unit) WHERE deleted IS FALSE; + +COMMENT ON TABLE reserves.term IS $$ +Term + +This table holds designators for the terms an organization uses. Some +popular examples are 'Spring 2016' or 'Intersession 2015-6'. +$$; + +COMMENT ON COLUMN reserves.term.id IS 'system generated'; +COMMENT ON COLUMN reserves.term.created IS 'system generated'; +COMMENT ON COLUMN reserves.term.last_modified IS 'system generated'; +COMMENT ON COLUMN reserves.term.finish IS 'null = no end date'; + +CREATE TRIGGER update_reserves_term_modtime + BEFORE UPDATE ON reserves.term FOR EACH ROW + EXECUTE PROCEDURE reserves.update_last_modified_column(); + + +------------------------------------------------------------------------- +-- reserves.course +------------------------------------------------------------------------- +CREATE TABLE reserves.course ( + id SERIAL PRIMARY KEY, + created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + last_modified TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + course_name TEXT NOT NULL, + course_number TEXT NOT NULL, + course_notes TEXT, + dept_id INT REFERENCES reserves.department(id) + ON DELETE CASCADE NOT NULL, + deleted BOOL DEFAULT false NOT NULL, + CONSTRAINT res_one_course_name_per_dept_ UNIQUE (course_name, dept_id), + CONSTRAINT res_one_course_num_per_dept UNIQUE (course_number, dept_id) +-- Constraints needed to prevent duplication of course_name or number within +-- a department. +); +CREATE RULE protect_course_delete AS + ON DELETE TO reserves.course DO INSTEAD + UPDATE reserves.course SET deleted = TRUE WHERE OLD.id = reserves.course.id; + +CREATE UNIQUE INDEX res_course_name_once_per_dept ON reserves.course (course_name, dept_id) WHERE deleted IS FALSE; +CREATE UNIQUE INDEX res_course_num_once_per_dept ON reserves.course (course_number, dept_id) WHERE deleted IS FALSE; +CREATE INDEX res_course_dept_idx ON reserves.course (dept_id) WHERE deleted IS FALSE; + +COMMENT ON TABLE reserves.course IS $$ +Course + +This table holds a list of courses, wach pointing to the department in +which the course is offered. +$$; + +COMMENT ON COLUMN reserves.course.id IS 'system generated'; +COMMENT ON COLUMN reserves.course.created IS 'system generated'; +COMMENT ON COLUMN reserves.course.last_modified IS 'system generated'; + +CREATE TRIGGER update_reserves_course_modtime + BEFORE UPDATE ON reserves.course FOR EACH ROW + EXECUTE PROCEDURE reserves.update_last_modified_column(); + + +------------------------------------------------------------------------- +-- reserves.section +------------------------------------------------------------------------- +CREATE TABLE reserves.section ( + id SERIAL PRIMARY KEY, + created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + last_modified TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + reserves_desk_org_unit INT NOT NULL REFERENCES actor.org_unit (id), + section_code TEXT, + campus TEXT, + course_id INT NOT NULL REFERENCES reserves.course (id) + ON DELETE CASCADE, + term_id INT NOT NULL REFERENCES reserves.term (id), + public BOOL DEFAULT true NOT NULL, + deleted BOOL DEFAULT false NOT NULL +); + +CREATE UNIQUE INDEX res_section_once_per_course_term ON reserves.section + (section_code, course_id, term_id) WHERE deleted IS FALSE; +CREATE INDEX res_section_course_idx ON reserves.section(course_id) WHERE deleted IS FALSE; + +CREATE RULE protect_section_delete AS + ON DELETE TO reserves.section DO INSTEAD + UPDATE reserves.section SET deleted = TRUE WHERE OLD.id = reserves.section.id; + +COMMENT ON TABLE reserves.section IS $$ +Section + +This table holds all sections for a course/term. All sections for a course in a given term +must have unique names (to tell them apart). +$$; + +COMMENT ON COLUMN reserves.section.id IS 'system generated'; +COMMENT ON COLUMN reserves.section.created IS 'system generated'; +COMMENT ON COLUMN reserves.section.last_modified IS 'system generated'; + +CREATE TRIGGER update_reserves_section_modtime + BEFORE UPDATE ON reserves.section FOR EACH ROW + EXECUTE PROCEDURE reserves.update_last_modified_column(); + + +------------------------------------------------------------------------- +-- reserves.section_note +------------------------------------------------------------------------- +CREATE TABLE reserves.section_note ( + id SERIAL PRIMARY KEY, + created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + last_modified TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + section_id INT NOT NULL REFERENCES reserves.section (id) + ON DELETE CASCADE, + note TEXT NOT NULL, + public BOOL DEFAULT FALSE +); + +CREATE INDEX res_note_sect_id_idx ON reserves.section_note (section_id); + +COMMENT ON TABLE reserves.section_note IS $$ +Section Note + +This table contains optional notes for a section. There may be more than one note for +a section, and they may be designated public or private (default). +$$; + +COMMENT ON COLUMN reserves.section_note.id IS 'system generated'; +COMMENT ON COLUMN reserves.section_note.created IS 'system generated'; +COMMENT ON COLUMN reserves.section_note.last_modified IS 'system generated'; + +CREATE TRIGGER update_reserves_section_note_modtime + BEFORE UPDATE ON reserves.section_note FOR EACH ROW + EXECUTE PROCEDURE reserves.update_last_modified_column(); + + +------------------------------------------------------------------------- +-- reserves.faculty_section_map +------------------------------------------------------------------------- +CREATE TABLE reserves.faculty_section_map ( + id SERIAL PRIMARY KEY, + created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + last_modified TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + section_id INT NOT NULL REFERENCES reserves.section (id) + ON DELETE CASCADE, + usr_id INT NOT NULL REFERENCES actor.usr (id), + CONSTRAINT res_one_faculty_per_section + UNIQUE (usr_id, section_id) +-- You don't want Professor Plum to be listed more than once for a section. +); +CREATE INDEX res_faculty_sect_id_idx ON reserves.faculty_section_map (section_id); + +COMMENT ON TABLE reserves.faculty_section_map IS $$ +faculty_section_map + +This table contains a list of the faculty members assigned to a particular section. +There may be more than one faculty member for a section. +$$; + +COMMENT ON COLUMN reserves.faculty_section_map.id IS 'system generated'; +COMMENT ON COLUMN reserves.faculty_section_map.created IS 'system generated'; +COMMENT ON COLUMN reserves.faculty_section_map.last_modified IS 'system generated'; + +CREATE TRIGGER update_reserves_faculty_modtime + BEFORE UPDATE ON reserves.faculty_section_map FOR EACH ROW + EXECUTE PROCEDURE reserves.update_last_modified_column(); + + +------------------------------------------------------------------------- +-- reserves.copy_section_map +------------------------------------------------------------------------- +CREATE TABLE reserves.copy_section_map ( + id SERIAL PRIMARY KEY, + created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + last_modified TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, + copy_id INT NOT NULL REFERENCES asset.copy (id) + ON DELETE CASCADE, + section_id INT NOT NULL REFERENCES reserves.section (id) + ON DELETE CASCADE, + on_reserve_date DATE NOT NULL, + off_reserve_date DATE, + usr_id INT NOT NULL REFERENCES actor.usr (id), + personal_copy BOOL DEFAULT false NOT NULL, + deleted BOOL DEFAULT false NOT NULL, + CONSTRAINT res_one_item_per_section + UNIQUE (copy_id, section_id) +); +CREATE INDEX res_item_sect_id_idx ON reserves.copy_section_map (section_id) WHERE deleted IS FALSE; +CREATE UNIQUE INDEX res_item_once_per_section ON reserves.copy_section_ma (copy_id, section_id) WHERE deleted IS FALSE; + +CREATE RULE protect_item_delete AS + ON DELETE TO reserves.copy_section_map DO INSTEAD + UPDATE reserves.copy_section_map SET deleted = TRUE WHERE OLD.id = reserves.copy_section_map.id; + +COMMENT ON TABLE reserves.copy_section_map IS $$ +Copy_section_map + +This table contains a list of the items assigned to each section. +$$; + +COMMENT ON COLUMN reserves.copy_section_map.id IS 'system generated'; +COMMENT ON COLUMN reserves.copy_section_map.created IS 'system generated'; +COMMENT ON COLUMN reserves.copy_section_map.last_modified IS 'system generated'; + +CREATE TRIGGER update_reserves_copy_section_map + BEFORE UPDATE ON reserves.copy_section_map FOR EACH ROW + EXECUTE PROCEDURE reserves.update_last_modified_column(); + + +COMMIT; 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 e029263de2..55e1bc65f7 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -1603,7 +1603,9 @@ INSERT INTO permission.perm_list ( id, code, description ) VALUES ( 562, 'ADMIN_TAG_TABLE', oils_i18n_gettext( 562, 'Allow administration of MARC tag tables', 'ppl', 'description' )), ( 563, 'ADJUST_BILLS', oils_i18n_gettext( 563, - 'Allow a user to adjust a bill (generally to zero)', 'ppl', 'description' )) + 'Allow a user to adjust a bill (generally to zero)', 'ppl', 'description' )), + ( 564, 'ADMIN_RESERVES', oils_i18n_gettext( 564, + 'Allow modification/deletion of course reserves data', 'ppl', 'description' )) ; SELECT SETVAL('permission.perm_list_id_seq'::TEXT, 1000); -- 2.11.0