From e3e0e6cbd5c167aa5bb984d80443bf6502631fbc Mon Sep 17 00:00:00 2001 From: miker Date: Sun, 24 Jul 2005 21:12:56 +0000 Subject: [PATCH] moving sql files to dir matching the Open-ILS driver name (sorry for killing the history...) git-svn-id: svn://svn.open-ils.org/ILS/trunk@1395 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 417 ++++++++++++++++++ Open-ILS/src/sql/Pg/005.schema.actors.sql | 389 ++++++++++++++++ .../src/sql/Pg/006.schema.permissions.sql | 216 +++++++++ Open-ILS/src/sql/Pg/010.schema.biblio.sql | 43 ++ Open-ILS/src/sql/Pg/020.schema.functions.sql | 87 ++++ Open-ILS/src/sql/Pg/030.schema.metabib.sql | 128 ++++++ Open-ILS/src/sql/Pg/040.schema.asset.sql | 135 ++++++ Open-ILS/src/sql/Pg/080.schema.money.sql | 139 ++++++ Open-ILS/src/sql/Pg/090.schema.action.sql | 164 +++++++ Open-ILS/src/sql/Pg/800.fkeys.sql | 77 ++++ Open-ILS/src/sql/Pg/900.audit-tables.sql | 42 ++ Open-ILS/src/sql/Pg/build-db-Postgres.sh | 13 + 12 files changed, 1850 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/002.schema.config.sql create mode 100644 Open-ILS/src/sql/Pg/005.schema.actors.sql create mode 100644 Open-ILS/src/sql/Pg/006.schema.permissions.sql create mode 100644 Open-ILS/src/sql/Pg/010.schema.biblio.sql create mode 100644 Open-ILS/src/sql/Pg/020.schema.functions.sql create mode 100644 Open-ILS/src/sql/Pg/030.schema.metabib.sql create mode 100644 Open-ILS/src/sql/Pg/040.schema.asset.sql create mode 100644 Open-ILS/src/sql/Pg/080.schema.money.sql create mode 100644 Open-ILS/src/sql/Pg/090.schema.action.sql create mode 100644 Open-ILS/src/sql/Pg/800.fkeys.sql create mode 100644 Open-ILS/src/sql/Pg/900.audit-tables.sql create mode 100755 Open-ILS/src/sql/Pg/build-db-Postgres.sh diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql new file mode 100644 index 0000000000..84dd426bc1 --- /dev/null +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -0,0 +1,417 @@ + +DROP SCHEMA config CASCADE; + +BEGIN; +CREATE SCHEMA config; +COMMENT ON SCHEMA config IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * The config schema holds static configuration data for the + * Open-ILS installation. + * + * **** + * + * 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. + */ +$$; + + +CREATE TABLE config.bib_source ( + id SERIAL PRIMARY KEY, + quality INT CHECK ( quality BETWEEN 0 AND 100 ), + source TEXT NOT NULL UNIQUE +); +COMMENT ON TABLE config.bib_source IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Valid sources of MARC records + * + * This is table is used to set up the relative "quality" of each + * MARC source, such as OCLC. + * + * **** + * + * 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. + */ +$$; + + +INSERT INTO config.bib_source (quality, source) VALUES (90, 'OcLC'); +INSERT INTO config.bib_source (quality, source) VALUES (10, 'System Local'); + +CREATE TABLE config.standing ( + id SERIAL PRIMARY KEY, + value TEXT NOT NULL UNIQUE +); +COMMENT ON TABLE config.standing IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Patron Standings + * + * This table contains the values that can be applied to a patron + * by a staff member. These values should not be changed, other + * that for translation, as the ID column is currently a "magic + * number" in the source. :( + * + * **** + * + * 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. + */ +$$; + +INSERT INTO config.standing (value) VALUES ('Good'); +INSERT INTO config.standing (value) VALUES ('Barred'); + + + +CREATE TABLE config.metabib_field ( + id SERIAL PRIMARY KEY, + field_class TEXT NOT NULL CHECK (lower(field_class) IN ('title','author','subject','keyword','series')), + name TEXT NOT NULL UNIQUE, + xpath TEXT NOT NULL +); +COMMENT ON TABLE config.metabib_field IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * XPath used for WoRMing + * + * This table contains the XPath used to chop up MODS into it's + * indexable parts. Each XPath entry is named and assigned to + * a "class" of either title, subject, author, keyword or series. + * + * + * **** + * + * 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. + */ +$$; + + +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'series', 'seriestitle', $$//mods:mods/mods:relatedItem[@type="series"]/mods:titleInfo$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'title', 'abbreviated', $$//mods:mods/mods:titleInfo[mods:title and (@type='abreviated')]$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'title', 'translated', $$//mods:mods/mods:titleInfo[mods:title and (@type='translated')]$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'title', 'uniform', $$//mods:mods/mods:titleInfo[mods:title and (@type='uniform')]$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'title', 'proper', $$//mods:mods/mods:titleInfo[mods:title and not (@type)]$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'author', 'corporate', $$//mods:mods/mods:name[@type='corporate']/mods:namePart[../mods:role/mods:text[text()='creator']]$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'author', 'personal', $$//mods:mods/mods:name[@type='personal']/mods:namePart[../mods:role/mods:text[text()='creator']]$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'author', 'conference', $$//mods:mods/mods:name[@type='conference']/mods:namePart[../mods:role/mods:text[text()='creator']]$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'author', 'other', $$//mods:mods/mods:name[@type='personal']/mods:namePart[not(../mods:role)]$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'subject', 'geographic', $$//mods:mods/mods:subject/mods:geographic$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'subject', 'name', $$//mods:mods/mods:subject/mods:name$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'subject', 'temporal', $$//mods:mods/mods:subject/mods:temporal$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'subject', 'topic', $$//mods:mods/mods:subject/mods:topic$$ ); +-- INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'subject', 'genre', $$//mods:mods/mods:genre$$ ); +INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'keyword', 'keyword', $$//mods:mods/*[not(local-name()='originInfo')]$$ ); -- /* to fool vim */ + +CREATE TABLE config.identification_type ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE +); +COMMENT ON TABLE config.identification_type IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Types of valid patron identification. + * + * Each patron must display at least one valid form of identification + * in order to get a library card. This table lists those forms. + * + * + * **** + * + * 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. + */ +$$; + + +INSERT INTO config.identification_type ( name ) VALUES ( 'Drivers Licence' ); +INSERT INTO config.identification_type ( name ) VALUES ( 'Voter Card' ); +INSERT INTO config.identification_type ( name ) VALUES ( 'Two Utility Bills' ); +INSERT INTO config.identification_type ( name ) VALUES ( 'State ID' ); +INSERT INTO config.identification_type ( name ) VALUES ( 'SSN' ); + +CREATE TABLE config.rule_circ_duration ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE CHECK ( name ~ '^\\w+$' ), + extended INTERVAL NOT NULL, + normal INTERVAL NOT NULL, + shrt INTERVAL NOT NULL, + max_renewals INT NOT NULL +); +COMMENT ON TABLE config.rule_circ_duration IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Circulation Duration rules + * + * Each circulation is given a duration based on one of these rules. + * + * + * **** + * + * 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. + */ +$$; + +INSERT INTO config.rule_circ_duration VALUES (DEFAULT, '2wk_default', '21 days', '14 days', '7 days', 2); + + +CREATE TABLE config.rule_max_fine ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE CHECK ( name ~ '^\\w+$' ), + amount NUMERIC(6,2) NOT NULL +); +COMMENT ON TABLE config.rule_max_fine IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Circulation Max Fine rules + * + * Each circulation is given a maximum fine based on one of + * these rules. + * + * + * **** + * + * 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. + */ +$$; + +INSERT INTO rule_max_fine VALUES (DEFAULT, 'books', 50.00); + + +CREATE TABLE config.rule_recuring_fine ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE CHECK ( name ~ '^\\w+$' ), + high NUMERIC(6,2) NOT NULL, + normal NUMERIC(6,2) NOT NULL, + low NUMERIC(6,2) NOT NULL, + recurance_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL +); +COMMENT ON TABLE config.rule_recuring_fine IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Circulation Recuring Fine rules + * + * Each circulation is given a recuring fine amount based on one of + * these rules. The recurance_interval should not be any shorter + * than the interval between runs of the fine_processor.pl script + * (which is run from CRON), or you could miss fines. + * + * + * **** + * + * 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. + */ +$$; + +INSERT INTO rule_recuring_fine VALUES (1, 'books', 0.50, 0.10, 0.10, '1 day'); + + +CREATE TABLE config.rule_age_hold_protect ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE CHECK ( name ~ '^\\w+$' ), + age INTERVAL NOT NULL, + prox INT NOT NULL +); +COMMENT ON TABLE config.rule_age_hold_protect IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Hold Item Age Protection rules + * + * A hold request can only capture new(ish) items when they are + * within a particular proximity of the home_ou of the requesting + * user. The proximity ('prox' column) is calculated by counting + * the number of tree edges beween the user's home_ou and the owning_lib + * of the copy that could fulfill the hold. + * + * + * **** + * + * 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. + */ +$$; + +INSERT INTO rule_age_hold_protect VALUES (DEFAULT, '3month', '3 mons', 3); +INSERT INTO rule_age_hold_protect VALUES (DEFAULT, '6month', '6 mons', 2); + + +CREATE TABLE config.copy_status ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE, + holdable BOOL NOT NULL DEFAULT FALSE +); +COMMENT ON TABLE config.copy_status IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Copy Statuses + * + * The available copy statuses, and whether a copy in that + * status is available for hold request capture. 0 (zero) is + * the only special number in this set, meaning that the item + * is available for imediate checkout, and is counted as available + * in the OPAC. + * + * Statuses with an ID below 100 are not removable, and have special + * meaning in the code. Do not change them except to translate the + * textual name. + * + * You may add and remove statuses above 100, and these can be used + * to remove items from normal circulation without affecting the rest + * of the copy's values or it's location. + * + * **** + * + * 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. + */ +$$; + +INSERT INTO config.copy_status (id,name,holdable) VALUES (0,'Available','t'); +INSERT INTO config.copy_status (name,holdable) VALUES ('Checked out','t'); +INSERT INTO config.copy_status (name) VALUES ('Bindery'); +INSERT INTO config.copy_status (name) VALUES ('Lost'); +INSERT INTO config.copy_status (name) VALUES ('Missing'); +INSERT INTO config.copy_status (name,holdable) VALUES ('In process','t'); +INSERT INTO config.copy_status (name,holdable) VALUES ('In transit','t'); +INSERT INTO config.copy_status (name,holdable) VALUES ('Reshelving','t'); +INSERT INTO config.copy_status (name) VALUES ('On holds shelf'); +INSERT INTO config.copy_status (name,holdable) VALUES ('On order','t'); +INSERT INTO config.copy_status (name) VALUES ('ILL'); +INSERT INTO config.copy_status (name) VALUES ('Cataloging'); +INSERT INTO config.copy_status (name) VALUES ('Reserves'); +INSERT INTO config.copy_status (name) VALUES ('Discard/Weed'); + +SELECT SETVAL('config.copy_status_id_seq'::TEXT, 100); + + +CREATE TABLE config.net_access_level ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE +); +COMMENT ON TABLE config.net_access_level IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Patron Network Access level + * + * This will be used to inform the in-library firewall of how much + * internet access the using patron should be allowed. + * + * **** + * + * 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. + */ +$$; + +INSERT INTO config.net_access_level (name) VALUES ('Restricted'); +INSERT INTO config.net_access_level (name) VALUES ('Full'); +INSERT INTO config.net_access_level (name) VALUES ('None'); + + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql new file mode 100644 index 0000000000..a28744db28 --- /dev/null +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -0,0 +1,389 @@ +DROP SCHEMA actor CASCADE; + +BEGIN; +CREATE SCHEMA actor; +COMMENT ON SCHEMA actor IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Schema: actor + * + * Holds all tables pertaining to users and libraries (org units). + * + * **** + * + * 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. + */ +$$; + +CREATE TABLE actor.usr ( + id SERIAL PRIMARY KEY, + card INT UNIQUE, -- active card + profile INT NOT NULL, -- patron profile + usrname TEXT NOT NULL UNIQUE, + email TEXT, + passwd TEXT NOT NULL, + standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id), + ident_type INT NOT NULL REFERENCES config.identification_type (id), + ident_value TEXT NOT NULL, + ident_type2 INT REFERENCES config.identification_type (id), + ident_value2 TEXT, + net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id), + photo_url TEXT, + prefix TEXT, + first_given_name TEXT NOT NULL, + second_given_name TEXT, + family_name TEXT NOT NULL, + suffix TEXT, + day_phone TEXT, + evening_phone TEXT, + other_phone TEXT, + mailing_address INT, + billing_address INT, + home_ou INT NOT NULL, + dob DATE NOT NULL, + active BOOL NOT NULL DEFAULT TRUE, + master_account BOOL NOT NULL DEFAULT FALSE, + super_user BOOL NOT NULL DEFAULT FALSE, + usrgroup SERIAL NOT NULL, + claims_returned_count INT NOT NULL DEFAULT 0, + credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00, + last_xact_id TEXT NOT NULL DEFAULT 'none', + alert_message TEXT, + create_date DATE NOT NULL DEFAULT now()::DATE, + expire_date DATE NOT NULL DEFAULT (now() + '3 years'::INTERVAL)::DATE +); +COMMENT ON TABLE actor.usr IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * User objects + * + * This table contains the core User objects that describe both + * staff members and patrons. The difference between the two + * types of users is based on the user's permissions. + * + * **** + * + * 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. + */ +$$; + +CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou); +CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address); +CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address); + +CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name)); +CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name)); +CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name)); + +CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email)); + +CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone)); +CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone)); +CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone)); + +CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value)); +CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2)); + +CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$ + BEGIN + NEW.passwd = MD5( NEW.passwd ); + RETURN NEW; + END; +$$ LANGUAGE PLPGSQL; + +CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$ + BEGIN + IF NEW.passwd <> OLD.passwd THEN + NEW.passwd = MD5( NEW.passwd ); + END IF; + RETURN NEW; + END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER actor_crypt_pw_update_trigger + BEFORE UPDATE ON actor.usr FOR EACH ROW + EXECUTE PROCEDURE actor.crypt_pw_update (); + +CREATE TRIGGER actor_crypt_pw_insert_trigger + BEFORE INSERT ON actor.usr FOR EACH ROW + EXECUTE PROCEDURE actor.crypt_pw_insert (); + +-- Just so that there is a user... +INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value, home_ou ) + VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', '', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 ); + + +CREATE TABLE actor.usr_setting ( + id BIGSERIAL PRIMARY KEY, + usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE, + name TEXT NOT NULL, + value TEXT NOT NULL, + CONSTRAINT name_once_per_value UNIQUE (usr,name) +); +COMMENT ON TABLE actor.usr_setting IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * User objects + * + * This table contains any arbitrary settings that a client + * program would like to save for a user. + * + * **** + * + * 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. + */ +$$; + +CREATE TABLE actor.stat_cat ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL, + name TEXT NOT NULL, + opac_visible BOOL NOT NULL DEFAULT FALSE, + CONSTRAINT sc_once_per_owner UNIQUE (owner,name) +); +COMMENT ON TABLE actor.stat_cat IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * User Statistical Catagories + * + * Local data collected about Users is placed into a Statistical + * Catagory. Here's where those catagories are defined. + * + * **** + * + * 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. + */ +$$; + + +CREATE TABLE actor.stat_cat_entry ( + id SERIAL PRIMARY KEY, + stat_cat INT NOT NULL, + owner INT NOT NULL, + value TEXT NOT NULL, + CONSTRAINT sce_once_per_owner UNIQUE (owner,value) +); +COMMENT ON TABLE actor.stat_cat_entry IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * User Statistical Catagory Entries + * + * Local data collected about Users is placed into a Statistical + * Catagory. Each library can create entries into any of it's own + * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats. + * + * + * **** + * + * 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. + */ +$$; + + +CREATE TABLE actor.stat_cat_entry_usr_map ( + id BIGSERIAL PRIMARY KEY, + stat_cat_entry TEXT NOT NULL, + stat_cat INT NOT NULL, + target_usr INT NOT NULL, + CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat) +); +COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Statistical Catagory Entry to User map + * + * Records the stat_cat entries for each user. + * + * + * **** + * + * 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. + */ +$$; + +CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr); + +CREATE TABLE actor.card ( + id SERIAL PRIMARY KEY, + usr INT NOT NULL REFERENCES actor.usr (id), + barcode TEXT NOT NULL UNIQUE, + active BOOL NOT NULL DEFAULT TRUE +); +COMMENT ON TABLE actor.card IS $$ +/* + * Copyright (C) 2005 Georgia Public Library Service + * Mike Rylander + * + * Library Cards + * + * Each User has one or more library cards. The current "main" + * card is linked to here from the actor.usr table, and it is up + * to the consortium policy whether more than one card can be + * active for any one user at a given time. + * + * + * **** + * + * 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. + */ +$$; + +CREATE INDEX actor_card_usr_idx ON actor.card (usr); + +INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101'); + + +CREATE TABLE actor.org_unit_type ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL, + opac_label TEXT NOT NULL, + depth INT NOT NULL, + parent INT REFERENCES actor.org_unit_type (id), + can_have_vols BOOL NOT NULL DEFAULT TRUE, + can_have_users BOOL NOT NULL DEFAULT TRUE +); +CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent); + +-- The PINES levels +INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE ); +INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'System','Local Library System', 1, 1, FALSE, FALSE ); +INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 ); +INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 ); +INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 ); + +CREATE TABLE actor.org_unit ( + id SERIAL PRIMARY KEY, + parent_ou INT REFERENCES actor.org_unit (id), + ou_type INT NOT NULL REFERENCES actor.org_unit_type (id), + ill_address INT, + holds_address INT, + mailing_address INT, + billing_address INT, + shortname TEXT NOT NULL, + name TEXT NOT NULL +); +CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou); +CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type); +CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address); +CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address); +CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address); +CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address); + +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'CONS', 'Example Consortium'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS1', 'Example System 1'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS2', 'Example System 2'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR1', 'Example Branch 1'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL4', 'Example Sub-lib 1'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM4', 'Example Bookmobile 1'); + +CREATE TABLE actor.usr_address ( + id SERIAL PRIMARY KEY, + valid BOOL NOT NULL DEFAULT TRUE, + address_type TEXT NOT NULL DEFAULT 'MAILING', + usr INT NOT NULL REFERENCES actor.usr (id), + street1 TEXT NOT NULL, + street2 TEXT, + city TEXT NOT NULL, + county TEXT, + state TEXT NOT NULL, + country TEXT NOT NULL, + post_code TEXT NOT NULL +); + +CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1)); +CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2)); + +CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city)); +CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state)); +CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code)); + + +CREATE TABLE actor.org_address ( + id SERIAL PRIMARY KEY, + valid BOOL NOT NULL DEFAULT TRUE, + address_type TEXT NOT NULL DEFAULT 'MAILING', + org_unit INT NOT NULL REFERENCES actor.org_unit (id), + street1 TEXT NOT NULL, + street2 TEXT, + city TEXT NOT NULL, + county TEXT, + state TEXT NOT NULL, + country TEXT NOT NULL, + post_code TEXT NOT NULL +); + +INSERT INTO actor.org_address (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303'); +UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql new file mode 100644 index 0000000000..2df0a0b9f7 --- /dev/null +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -0,0 +1,216 @@ +DROP SCHEMA permission CASCADE; + +BEGIN; +CREATE SCHEMA permission; + +CREATE TABLE permission.perm_list ( + id SERIAL PRIMARY KEY, + code TEXT NOT NULL UNIQUE +); +CREATE INDEX perm_list_code_idx ON permission.perm_list (code); + +INSERT INTO permission.perm_list VALUES (-1,'EVERYTHING'); +INSERT INTO permission.perm_list VALUES (2, 'OPAC_LOGIN'); +INSERT INTO permission.perm_list VALUES (4, 'STAFF_LOGIN'); +INSERT INTO permission.perm_list VALUES (5, 'MR_HOLDS'); +INSERT INTO permission.perm_list VALUES (6, 'TITLE_HOLDS'); +INSERT INTO permission.perm_list VALUES (7, 'VOLUME_HOLDS'); +INSERT INTO permission.perm_list VALUES (8, 'COPY_HOLDS'); +INSERT INTO permission.perm_list VALUES (9, 'REQUEST_HOLDS'); +INSERT INTO permission.perm_list VALUES (10, 'REQUEST_HOLDS_OVERRIDE'); +INSERT INTO permission.perm_list VALUES (11, 'VIEW_HOLDS'); +INSERT INTO permission.perm_list VALUES (13, 'DELETE_HOLDS'); +INSERT INTO permission.perm_list VALUES (14, 'UPDATE_HOLDS'); +INSERT INTO permission.perm_list VALUES (15, 'RENEW_CIRC'); +INSERT INTO permission.perm_list VALUES (16, 'VIEW_USER_FINES_SUMMARY'); +INSERT INTO permission.perm_list VALUES (17, 'VIEW_USER_TRANSACTIONS'); +INSERT INTO permission.perm_list VALUES (18, 'UPDATE_MARC'); +INSERT INTO permission.perm_list VALUES (19, 'CREATE_ORIGINAL_MARC'); +INSERT INTO permission.perm_list VALUES (20, 'IMPORT_MARC'); +INSERT INTO permission.perm_list VALUES (21, 'CREATE_VOLUME'); +INSERT INTO permission.perm_list VALUES (22, 'UPDATE_VOLUME'); +INSERT INTO permission.perm_list VALUES (23, 'DELETE_VOLUME'); +INSERT INTO permission.perm_list VALUES (24, 'CREATE_COPY'); +INSERT INTO permission.perm_list VALUES (25, 'UPDATE_COPY'); +INSERT INTO permission.perm_list VALUES (26, 'DELETE_COPY'); +INSERT INTO permission.perm_list VALUES (27, 'RENEW_HOLD_OVERRIDE'); +INSERT INTO permission.perm_list VALUES (28, 'CREATE_USER'); +INSERT INTO permission.perm_list VALUES (29, 'UPDATE_USER'); +INSERT INTO permission.perm_list VALUES (30, 'DELETE_USER'); +INSERT INTO permission.perm_list VALUES (31, 'VIEW_USER'); +INSERT INTO permission.perm_list VALUES (32, 'COPY_CHECKIN'); +INSERT INTO permission.perm_list VALUES (33, 'CREATE_TRANSIT'); +INSERT INTO permission.perm_list VALUES (34, 'VIEW_PERMISSION'); +INSERT INTO permission.perm_list VALUES (35, 'CHECKIN_BYPASS_HOLD_FULFILL'); +INSERT INTO permission.perm_list VALUES (36, 'CREATE_PAYMENT'); + +SELECT SETVAL('permission.perm_list_id_seq'::TEXT, 37); + +CREATE TABLE permission.grp_tree ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL UNIQUE, + parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT +); +CREATE INDEX grp_tree_parent ON permission.grp_tree (parent); + +INSERT INTO grp_tree VALUES (1, 'Users', NULL); +INSERT INTO grp_tree VALUES (2, 'Patrons', 1); +INSERT INTO grp_tree VALUES (3, 'Staff', 1); +INSERT INTO grp_tree VALUES (4, 'Catalogers', 3); +INSERT INTO grp_tree VALUES (5, 'Circulators', 3); + +SELECT SETVAL('permission.grp_tree_id_seq'::TEXT, 6); + +CREATE TABLE permission.grp_perm_map ( + id SERIAL PRIMARY KEY, + grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE, + perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE, + depth INT NOT NULL, + CONSTRAINT perm_grp_once UNIQUE (grp,perm) +); + +INSERT INTO permission.grp_perm_map VALUES (1, 1, 2, 0); +INSERT INTO permission.grp_perm_map VALUES (12, 1, 5, 0); +INSERT INTO permission.grp_perm_map VALUES (13, 1, 6, 0); +INSERT INTO permission.grp_perm_map VALUES (15, 4, 8, 2); +INSERT INTO permission.grp_perm_map VALUES (22, 4, 18, 0); +INSERT INTO permission.grp_perm_map VALUES (23, 4, 19, 0); +INSERT INTO permission.grp_perm_map VALUES (24, 4, 20, 0); +INSERT INTO permission.grp_perm_map VALUES (38, 4, 21, 2); +INSERT INTO permission.grp_perm_map VALUES (34, 4, 22, 2); +INSERT INTO permission.grp_perm_map VALUES (39, 4, 23, 2); +INSERT INTO permission.grp_perm_map VALUES (40, 4, 24, 2); +INSERT INTO permission.grp_perm_map VALUES (35, 4, 25, 2); +INSERT INTO permission.grp_perm_map VALUES (11, 3, 4, 0); +INSERT INTO permission.grp_perm_map VALUES (14, 3, 7, 2); +INSERT INTO permission.grp_perm_map VALUES (16, 3, 9, 0); +INSERT INTO permission.grp_perm_map VALUES (17, 3, 11, 0); +INSERT INTO permission.grp_perm_map VALUES (19, 3, 15, 0); +INSERT INTO permission.grp_perm_map VALUES (20, 3, 16, 0); +INSERT INTO permission.grp_perm_map VALUES (21, 3, 17, 0); +INSERT INTO permission.grp_perm_map VALUES (26, 3, 27, 0); +INSERT INTO permission.grp_perm_map VALUES (27, 3, 28, 0); +INSERT INTO permission.grp_perm_map VALUES (28, 3, 29, 0); +INSERT INTO permission.grp_perm_map VALUES (29, 3, 30, 0); +INSERT INTO permission.grp_perm_map VALUES (44, 3, 31, 0); +INSERT INTO permission.grp_perm_map VALUES (30, 3, 32, 0); +INSERT INTO permission.grp_perm_map VALUES (31, 3, 33, 0); +INSERT INTO permission.grp_perm_map VALUES (32, 3, 34, 0); +INSERT INTO permission.grp_perm_map VALUES (33, 3, 35, 0); +INSERT INTO permission.grp_perm_map VALUES (41, 3, 36, 0); + +SELECT SETVAL('permission.grp_perm_map_id_seq'::TEXT, 44); + + +CREATE TABLE permission.usr_perm_map ( + id SERIAL PRIMARY KEY, + usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE, + perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE, + depth INT NOT NULL, + CONSTRAINT perm_usr_once UNIQUE (usr,perm) +); + +CREATE TABLE permission.usr_grp_map ( + id SERIAL PRIMARY KEY, + usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE, + grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE, + CONSTRAINT usr_grp_once UNIQUE (usr,grp) +); + +-- Admin user +INSERT INTO permission.usr_perm_map (usr,perm,depth) VALUES (1,-1,0); + +CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$ + SELECT a.* + FROM connectby('permission.grp_tree','parent','id','name',$1,'100','.') + AS t(keyid text, parent_keyid text, level int, branch text,pos int) + JOIN permission.grp_tree a ON a.id = t.keyid + ORDER BY + CASE WHEN a.parent IS NULL + THEN 0 + ELSE 1 + END, a.name; +$$ LANGUAGE SQL STABLE; + +CREATE OR REPLACE FUNCTION permission.usr_perms ( iuser INT ) RETURNS SETOF permission.usr_perm_map AS $$ +DECLARE + u_perm permission.usr_perm_map%ROWTYPE; + grp permission.usr_grp_map%ROWTYPE; + g_list permission.grp_tree%ROWTYPE; +BEGIN + FOR u_perm IN SELECT * FROM permission.usr_perm_map WHERE usr = iuser LOOP + RETURN NEXT u_perm; + END LOOP; + + FOR g_list IN SELECT * + FROM permission.grp_ancestors( + ( SELECT u.profile + FROM actor.usr u + WHERE u.id = iuser + ) + ) + LOOP + + FOR u_perm IN SELECT DISTINCT -p.id, iuser AS usr, p.perm, p.depth + FROM permission.grp_perm_map p + WHERE p.grp = g_list.id LOOP + + RETURN NEXT u_perm; + + END LOOP; + END LOOP; + + FOR grp IN SELECT * + FROM permission.usr_grp_map + WHERE usr = iuser LOOP + + FOR g_list IN SELECT * + FROM permission.grp_ancestors( grp.grp ) LOOP + + FOR u_perm IN SELECT DISTINCT -p.id, iuser AS usr, p.perm, p.depth + FROM permission.grp_perm_map p + JOIN permission.usr_grp_map m ON (m.grp = p.grp) + WHERE m.grp = g_list.id LOOP + + RETURN NEXT u_perm; + + END LOOP; + END LOOP; + END LOOP; + + RETURN; +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT, target INT ) RETURNS BOOL AS $$ +DECLARE + r_usr actor.usr%ROWTYPE; + r_perm permission.usr_perm_map%ROWTYPE; +BEGIN + + SELECT * INTO r_usr FROM actor.usr WHERE id = iuser; + + FOR r_perm IN SELECT * + FROM permission.usr_perms(iuser) p + JOIN permission.perm_list l + ON (l.id = p.perm) + WHERE l.code = tperm + OR p.perm = -1 LOOP + + PERFORM * + FROM actor.org_unit_descendants(target,r_perm.depth) + WHERE id = r_usr.home_ou; + + IF FOUND THEN + RETURN TRUE; + ELSE + RETURN FALSE; + END IF; + END LOOP; + + RETURN FALSE; +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/010.schema.biblio.sql b/Open-ILS/src/sql/Pg/010.schema.biblio.sql new file mode 100644 index 0000000000..f844c3d898 --- /dev/null +++ b/Open-ILS/src/sql/Pg/010.schema.biblio.sql @@ -0,0 +1,43 @@ +DROP SCHEMA biblio CASCADE; + +BEGIN; +CREATE SCHEMA biblio; + +CREATE SEQUENCE biblio.autogen_tcn_value_seq; +CREATE FUNCTION biblio.next_autogen_tcn_value () RETURNS TEXT AS $$ + BEGIN RETURN nextval('biblio.autogen_tcn_value_seq'::TEXT); END; +$$ LANGUAGE PLPGSQL; + +CREATE TABLE biblio.record_entry ( + id BIGSERIAL PRIMARY KEY, + fingerprint TEXT, + tcn_source TEXT NOT NULL DEFAULT 'AUTOGEN', + tcn_value TEXT NOT NULL DEFAULT biblio.next_autogen_tcn_value(), + creator INT NOT NULL DEFAULT 1, + editor INT NOT NULL DEFAULT 1, + create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), + edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), + active BOOL NOT NULL DEFAULT TRUE, + deleted BOOL NOT NULL DEFAULT FALSE, + source INT, + marc TEXT NOT NULL, + last_xact_id TEXT NOT NULL +); +CREATE INDEX biblio_record_entry_creator_idx ON biblio.record_entry ( creator ); +CREATE INDEX biblio_record_entry_editor_idx ON biblio.record_entry ( editor ); +CREATE UNIQUE INDEX biblio_record_unique_tcn ON biblio.record_entry (tcn_source,tcn_value) WHERE deleted IS FALSE; + +CREATE TABLE biblio.record_note ( + id BIGSERIAL PRIMARY KEY, + record BIGINT NOT NULL, + value TEXT NOT NULL, + creator INT NOT NULL DEFAULT 1, + editor INT NOT NULL DEFAULT 1, + create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), + edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now() +); +CREATE INDEX biblio_record_note_record_idx ON biblio.record_note ( record ); +CREATE INDEX biblio_record_note_creator_idx ON biblio.record_note ( creator ); +CREATE INDEX biblio_record_note_editor_idx ON biblio.record_note ( editor ); + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql new file mode 100644 index 0000000000..059aa79c22 --- /dev/null +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -0,0 +1,87 @@ +CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$ +SELECT + CASE WHEN $1 IS NULL + THEN $2 + WHEN $2 IS NULL + THEN $1 + ELSE $1 || ' ' || $2 + END; +$$ LANGUAGE SQL STABLE; + +CREATE AGGREGATE public.agg_text ( + sfunc = public.text_concat, + basetype = text, + stype = text +); + +CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$ +SELECT + CASE WHEN $1 IS NULL + THEN $2 + WHEN $2 IS NULL + THEN $1 + ELSE $1 || ' ' || $2 + END; +$$ LANGUAGE SQL STABLE; + +CREATE AGGREGATE public.agg_tsvector ( + sfunc = public.tsvector_concat, + basetype = tsvector, + stype = tsvector +); + +CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$ + BEGIN + RETURN $1::regclass; + END; +$$ language 'plpgsql'; + + +CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$ + SELECT a.* + FROM connectby('actor.org_unit','id','parent_ou','name',$1,'100','.') + AS t(keyid text, parent_keyid text, level int, branch text,pos int) + JOIN actor.org_unit a ON a.id = t.keyid + ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; +$$ LANGUAGE SQL STABLE; + +CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$ + SELECT a.* + FROM connectby('actor.org_unit','parent_ou','id','name',$1,'100','.') + AS t(keyid text, parent_keyid text, level int, branch text,pos int) + JOIN actor.org_unit a ON a.id = t.keyid + ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; +$$ LANGUAGE SQL STABLE; + + + +CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$ + SELECT a.* + FROM connectby('actor.org_unit','id','parent_ou','name', + (SELECT x.id + FROM actor.org_unit_ancestors($1) x + JOIN actor.org_unit_type y ON x.ou_type = y.id + WHERE y.depth = $2) + ,'100','.') + AS t(keyid text, parent_keyid text, level int, branch text,pos int) + JOIN actor.org_unit a ON a.id = t.keyid + ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; +$$ LANGUAGE SQL STABLE; + +CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS ' + SELECT * + FROM actor.org_unit_ancestors($1) + UNION + SELECT * + FROM actor.org_unit_descendants($1); +' LANGUAGE SQL STABLE; + + +CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS ' + SELECT COUNT(id)::INT FROM ( + select * from (SELECT id FROM actor.org_unit_ancestors($1) UNION SELECT id FROM actor.org_unit_ancestors($2)) x + EXCEPT + select * from (SELECT id FROM actor.org_unit_ancestors($1) INTERSECT SELECT id FROM actor.org_unit_ancestors($2)) y) z; +' LANGUAGE SQL STABLE; + + diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql new file mode 100644 index 0000000000..32d61802f3 --- /dev/null +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -0,0 +1,128 @@ +DROP SCHEMA metabib CASCADE; + +BEGIN; +CREATE SCHEMA metabib; + +CREATE TABLE metabib.metarecord ( + id BIGSERIAL PRIMARY KEY, + fingerprint TEXT NOT NULL, + master_record BIGINT, + mods TEXT +); +CREATE INDEX metabib_metarecord_master_record_idx ON metabib.metarecord (master_record); +CREATE INDEX metabib_metarecord_fingerprint_idx ON metabib.metarecord (fingerprint); + +CREATE TABLE metabib.title_field_entry ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL, + field INT NOT NULL, + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE TRIGGER metabib_title_field_entry_fti_trigger + BEFORE UPDATE OR INSERT ON metabib.title_field_entry + FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + + +CREATE TABLE metabib.author_field_entry ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL, + field INT NOT NULL, + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE TRIGGER metabib_author_field_entry_fti_trigger + BEFORE UPDATE OR INSERT ON metabib.author_field_entry + FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + + +CREATE TABLE metabib.subject_field_entry ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL, + field INT NOT NULL, + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE TRIGGER metabib_subject_field_entry_fti_trigger + BEFORE UPDATE OR INSERT ON metabib.subject_field_entry + FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + + +CREATE TABLE metabib.keyword_field_entry ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL, + field INT NOT NULL, + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE TRIGGER metabib_keyword_field_entry_fti_trigger + BEFORE UPDATE OR INSERT ON metabib.keyword_field_entry + FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + +CREATE TABLE metabib.series_field_entry ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL, + field INT NOT NULL, + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE TRIGGER metabib_series_field_entry_fti_trigger + BEFORE UPDATE OR INSERT ON metabib.series_field_entry + FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + +CREATE TABLE metabib.rec_descriptor ( + id BIGSERIAL PRIMARY KEY, + record BIGINT, + item_type TEXT, + item_form TEXT, + bib_level TEXT, + control_type TEXT, + char_encoding TEXT, + enc_level TEXT, + cat_form TEXT, + pub_status TEXT, + item_lang TEXT, + audience TEXT +); +CREATE INDEX metabib_rec_descriptor_record_idx ON metabib.rec_descriptor (record); +/* We may not need these... + +CREATE INDEX metabib_rec_descriptor_item_type_idx ON metabib.rec_descriptor (item_type); +CREATE INDEX metabib_rec_descriptor_item_form_idx ON metabib.rec_descriptor (item_form); +CREATE INDEX metabib_rec_descriptor_bib_level_idx ON metabib.rec_descriptor (bib_level); +CREATE INDEX metabib_rec_descriptor_control_type_idx ON metabib.rec_descriptor (control_type); +CREATE INDEX metabib_rec_descriptor_char_encoding_idx ON metabib.rec_descriptor (char_encoding); +CREATE INDEX metabib_rec_descriptor_enc_level_idx ON metabib.rec_descriptor (enc_level); +CREATE INDEX metabib_rec_descriptor_cat_form_idx ON metabib.rec_descriptor (cat_form); +CREATE INDEX metabib_rec_descriptor_pub_status_idx ON metabib.rec_descriptor (pub_status); +CREATE INDEX metabib_rec_descriptor_item_lang_idx ON metabib.rec_descriptor (item_lang); +CREATE INDEX metabib_rec_descriptor_audience_idx ON metabib.rec_descriptor (audience); + +*/ + + +CREATE TABLE metabib.full_rec ( + id BIGSERIAL PRIMARY KEY, + record BIGINT NOT NULL, + tag CHAR(3) NOT NULL, + ind1 CHAR(1), + ind2 CHAR(1), + subfield CHAR(1), + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE INDEX metabib_full_rec_record_idx ON metabib.full_rec (record); +CREATE TRIGGER metabib_full_rec_fti_trigger + BEFORE UPDATE OR INSERT ON metabib.full_rec + FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + +CREATE TABLE metabib.metarecord_source_map ( + id BIGSERIAL PRIMARY KEY, + metarecord BIGINT NOT NULL, + source BIGINT NOT NULL +); +CREATE INDEX metabib_metarecord_source_map_metarecord_idx ON metabib.metarecord_source_map (metarecord); +CREATE INDEX metabib_metarecord_source_map_source_record_idx ON metabib.metarecord_source_map (source); + + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql new file mode 100644 index 0000000000..7e38df8be8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -0,0 +1,135 @@ +DROP SCHEMA asset CASCADE; + +BEGIN; + +CREATE SCHEMA asset; + +CREATE TABLE asset.copy_location ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL, + owning_lib INT NOT NULL REFERENCES actor.org_unit (id), + holdable BOOL NOT NULL DEFAULT TRUE, + opac_visible BOOL NOT NULL DEFAULT TRUE, + circulate BOOL NOT NULL DEFAULT TRUE +); +INSERT INTO asset.copy_location (name,owning_lib) VALUES ('Stacks',1); + +CREATE TABLE asset.copy ( + id BIGSERIAL PRIMARY KEY, + circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + creator BIGINT NOT NULL, + create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + editor BIGINT NOT NULL, + edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + barcode TEXT UNIQUE NOT NULL, + call_number BIGINT NOT NULL, + copy_number INT, + holdable BOOL NOT NULL DEFAULT TRUE, + status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED, + location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED, + loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ), + fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ), + circulate BOOL NOT NULL DEFAULT TRUE, + deposit BOOL NOT NULL DEFAULT FALSE, + deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00, + price NUMERIC(8,2) NOT NULL DEFAULT 0.00, + ref BOOL NOT NULL DEFAULT FALSE, + circ_modifier TEXT, + circ_as_type TEXT, + opac_visible BOOL NOT NULL DEFAULT TRUE +); +CREATE INDEX cp_cn_idx ON asset.copy (call_number); +CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number) WHERE status = 0; + +CREATE TABLE asset.copy_transparency ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL, + owner INT NOT NULL REFERENCES actor.org_unit (id), + circ_lib INT REFERENCES actor.org_unit (id), + holdable BOOL, + loan_duration INT CHECK ( loan_duration IN (1,2,3) ), + fine_level INT CHECK ( fine_level IN (1,2,3) ), + circulate BOOL, + deposit BOOL, + deposit_amount NUMERIC(6,2), + ref BOOL, + circ_modifier TEXT, + circ_as_type TEXT, + opac_visible BOOL, + CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name) +); + +CREATE TABLE asset.copy_tranparency_map ( + id BIGSERIAL PRIMARY KEY, + tansparency INT NOT NULL REFERENCES asset.copy_transparency (id), + target_copy INT NOT NULL UNIQUE REFERENCES asset.copy (id) +); +CREATE INDEX cp_tr_cp_idx ON asset.copy_tranparency_map (tansparency); + +CREATE TABLE asset.stat_cat_entry_transparency_map ( + id BIGSERIAL PRIMARY KEY, + stat_cat INT NOT NULL, -- needs ON DELETE CASCADE + stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE + owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE + CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat) +); + +CREATE TABLE asset.stat_cat ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL, + name TEXT NOT NULL, + opac_visible BOOL NOT NULL DEFAULT FALSE, + CONSTRAINT sc_once_per_owner UNIQUE (owner,name) +); + +CREATE TABLE asset.stat_cat_entry ( + id SERIAL PRIMARY KEY, + stat_cat INT NOT NULL, + owner INT NOT NULL, + value TEXT NOT NULL, + CONSTRAINT sce_once_per_owner UNIQUE (owner,value) +); + +CREATE TABLE asset.stat_cat_entry_copy_map ( + id BIGSERIAL PRIMARY KEY, + stat_cat INT NOT NULL, + stat_cat_entry INT NOT NULL, + owning_copy BIGINT NOT NULL, + CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat) +); + +CREATE TABLE asset.copy_note ( + id BIGSERIAL PRIMARY KEY, + owning_copy BIGINT NOT NULL, + creator BIGINT NOT NULL, + create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + title TEXT NOT NULL, + value TEXT NOT NULL +); + +CREATE TABLE asset.call_number ( + id bigserial PRIMARY KEY, + creator BIGINT NOT NULL, + create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + editor BIGINT NOT NULL, + edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + record bigint NOT NULL, + label TEXT NOT NULL, + owning_lib INT NOT NULL, + CONSTRAINT asset_call_number_label_once_per_lib UNIQUE (record, owning_lib, label) +); +CREATE INDEX asset_call_number_record_idx ON asset.call_number (record); +CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator); +CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor); + +CREATE TABLE asset.call_number_note ( + id BIGSERIAL PRIMARY KEY, + call_number BIGINT NOT NULL, + creator BIGINT NOT NULL, + create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + title TEXT NOT NULL, + value TEXT NOT NULL +); + + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql new file mode 100644 index 0000000000..42dec9d6f1 --- /dev/null +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -0,0 +1,139 @@ +DROP SCHEMA money CASCADE; + +BEGIN; + +CREATE SCHEMA money; + +CREATE TABLE money.billable_xact ( + id BIGSERIAL PRIMARY KEY, + usr INT NOT NULL, -- actor.usr.id + xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + xact_finish TIMESTAMP WITH TIME ZONE +); +CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr) WHERE xact_finish IS NULL; + +CREATE TABLE money.billing ( + id BIGSERIAL PRIMARY KEY, + xact BIGINT NOT NULL, -- money.billable_xact.id + amount NUMERIC(6,2) NOT NULL, + billing_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + note TEXT, + voided BOOL NOT NULL DEFALUT FALSE +); +CREATE INDEX m_b_xact_idx ON money.billing (xact); + +CREATE TABLE money.payment ( + id BIGSERIAL PRIMARY KEY, + xact BIGINT NOT NULL, -- money.billable_xact.id + amount NUMERIC(6,2) NOT NULL, + payment_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + note TEXT, + voided BOOL NOT NULL DEFALUT FALSE +); +CREATE INDEX m_p_xact_idx ON money.payment (xact); + +CREATE OR REPLACE VIEW money.payment_view AS + SELECT p.*,c.relname AS payment_type + FROM money.payment p + JOIN pg_class c ON (p.tableoid = c.oid); + +CREATE OR REPLACE VIEW money.transaction_billing_summary AS + SELECT xact, + note AS last_billing_note, + MAX(billing_ts) AS last_billing_ts, + SUM(COALESCE(amount,0)) AS total_owed + FROM money.billing + WHERE voided IS FALSE + GROUP BY xact,note + ORDER BY MAX(billing_ts); + +CREATE OR REPLACE VIEW money.transaction_payment_summary AS + SELECT xact, + note AS last_payment_note, + MAX(payment_ts) as last_payment_ts, + SUM(COALESCE(amount,0)) AS total_paid + FROM money.payment + WHERE voided IS FALSE + GROUP BY xact,note + ORDER BY MAX(payment_ts); + +CREATE OR REPLACE VIEW money.billable_xact_summary AS + SELECT xact.id AS id, + xact.usr AS usr, + xact.xact_start AS xact_start, + xact.xact_finish AS xact_finish, + credit.total_paid, + credit.last_payment_ts, + credit.last_payment_note, + debit.total_owed, + debit.last_billing_ts, + debit.last_billing_note, + COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed, + p.relname AS xact_type + FROM money.billable_xact xact + JOIN pg_class p ON (xact.tableoid = p.oid) + LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact) + LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact) + WHERE xact.xact_finish IS NULL; + +CREATE OR REPLACE VIEW money.usr_summary AS + SELECT usr, + SUM(total_paid) AS total_paid, + SUM(total_owed) AS total_owed, + SUM(balance_owed) AS balance_owed + FROM money.billable_xact_summary + GROUP BY 1; + +CREATE OR REPLACE VIEW money.usr_circulation_summary AS + SELECT usr, + SUM(total_paid) AS total_paid, + SUM(total_owed) AS total_owed, + SUM(balance_owed) AS balance_owed + FROM money.billable_xact_summary + WHERE xact_type = 'circulation' + GROUP BY 1; + +CREATE TABLE money.bnm_payment ( + amount_collected NUMERIC(6,2) NOT NULL, + accepting_usr INT NOT NULL +) INHERITS (money.payment); + +CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment); +CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact); +CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr); + +CREATE TABLE money.work_payment () INHERITS (money.bnm_payment); +CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact); +CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr); + +CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment); +CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact); +CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr); + +CREATE TABLE money.bnm_desk_payment ( + cash_drawer TEXT NOT NULL +) INHERITS (money.bnm_payment); + +CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment); +CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact); +CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr); + +CREATE TABLE money.check_payment ( + check_number TEXT NOT NULL +) INHERITS (money.bnm_desk_payment); +CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact); +CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr); + +CREATE TABLE money.credit_card_payment ( + cc_type TEXT NOT NULL, + cc_number TEXT NOT NULL, + expire_month INT NOT NULL, + expire_year INT NOT NULL, + approval_code TEXT NOT NULL +) INHERITS (money.bnm_desk_payment); +CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact); +CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr); + + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql new file mode 100644 index 0000000000..5bbfdc8cb6 --- /dev/null +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -0,0 +1,164 @@ +DROP SCHEMA action CASCADE; + +BEGIN; + +CREATE SCHEMA action; + +CREATE TABLE action.survey ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL REFERENCES actor.org_unit (id), + name TEXT NOT NULL, + description TEXT NOT NULL, + start_date DATE NOT NULL DEFAULT NOW(), + end_date DATE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL, + usr_summary BOOL NOT NULL DEFAULT FALSE, + opac BOOL NOT NULL DEFAULT FALSE, + poll BOOL NOT NULL DEFAULT FALSE, + required BOOL NOT NULL DEFAULT FALSE +); +CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name); + +CREATE TABLE action.survey_question ( + id SERIAL PRIMARY KEY, + survey INT NOT NULL REFERENCES action.survey, + question TEXT NOT NULL +); + +CREATE TABLE action.survey_answer ( + id SERIAL PRIMARY KEY, + question INT NOT NULL REFERENCES action.survey_question, + answer TEXT NOT NULL +); + +CREATE SEQUENCE action.survey_response_group_id_seq; + +CREATE TABLE action.survey_response ( + id BIGSERIAL PRIMARY KEY, + response_group_id INT, + usr INT, -- REFERENCES actor.usr + survey INT NOT NULL REFERENCES action.survey, + question INT NOT NULL REFERENCES action.survey_question, + answer INT NOT NULL REFERENCES action.survey_answer, + answer_date TIMESTAMP WITH TIME ZONE, + effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() +); +CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS ' +BEGIN + NEW.answer_date := NOW(); + RETURN NEW; +END; +' LANGUAGE 'plpgsql'; +CREATE TRIGGER action_survey_response_answer_date_fixup_tgr + BEFORE INSERT ON action.survey_response + FOR EACH ROW + EXECUTE PROCEDURE action.survey_response_answer_date_fixup (); + + +CREATE TABLE action.circulation ( + target_copy BIGINT NOT NULL, -- asset.copy.id + renewal BOOL NOT NULL DEFAULT FALSE, + circ_lib INT NOT NULL, -- actor.org_unit.id + duration_rule TEXT NOT NULL, -- name of "circ duration" rule + duration INTERVAL NOT NULL, -- derived from "circ duration" rule + renewal_remaining INT NOT NULL, -- derived from "circ duration" rule + recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule + recuring_fine NUMERIC(6,2) NOT NULL, -- derived from "circ fine" rule + max_fine_rule TEXT NOT NULL, -- name of "max fine" rule + max_fine NUMERIC(6,2) NOT NULL, -- derived from "max fine" rule + fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule + due_date TIMESTAMP WITH TIME ZONE NOT NULL, + stop_fines TEXT CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE')) +) INHERITS (money.billable_xact); +CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL; + +CREATE VIEW action.open_circulation AS + SELECT * + FROM action.circulation + WHERE xact_finish IS NULL + AND ( stop_fines IS NULL OR + stop_fines IN ('CLAIMSRETURNED','MAXFINES','LONGOVERDUE') + ) + ORDER BY due_date; + + +CREATE OR REPLACE VIEW action.open_cirulations AS + SELECT * + FROM action.circulation + WHERE xact_finish IS NULL; + +CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$ +BEGIN + IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'CLAIMSRETURNED' THEN + UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr; + END IF; + RETURN NEW; +END; +$$ LANGUAGE 'plpgsql'; +CREATE TRIGGER action_circulation_claims_returned + BEFORE UPDATE ON action.circulation + FOR EACH ROW + EXECUTE PROCEDURE action.circulation_claims_returned (); + + +CREATE TABLE action.hold_request ( + id SERIAL PRIMARY KEY, + request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + capture_time TIMESTAMP WITH TIME ZONE, + fulfillment_time TIMESTAMP WITH TIME ZONE, + checkin_time TIMESTAMP WITH TIME ZONE, + return_time TIMESTAMP WITH TIME ZONE, + prev_check_time TIMESTAMP WITH TIME ZONE, + expire_time TIMESTAMP WITH TIME ZONE, + requestor INT NOT NULL REFERENCES actor.usr (id), + usr INT NOT NULL REFERENCES actor.usr (id), + hold_type CHAR NOT NULL CHECK (hold_type IN ('M','T','V','C')), + holdable_formats TEXT, + phone_notify TEXT, + email_notify TEXT, + target BIGINT NOT NULL, -- see hold_type + selection_depth INT NOT NULL DEFAULT 0, + pickup_lib INT NOT NULL REFERENCES actor.org_unit, + current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL +); + + +CREATE TABLE action.hold_notification ( + id SERIAL PRIMARY KEY, + hold INT NOT NULL REFERENCES action.hold_request (id), + method TEXT NOT NULL, -- eh... + notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + note TEXT +); + +CREATE TABLE action.hold_copy_map ( + id SERIAL PRIMARY KEY, + hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE, + target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE, + CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy) +); + +CREATE TABLE action.transit_copy ( + id SERIAL PRIMARY KEY, + target_copy BIGINT NOT NULL asset.copy (id) ON DELETE CASCADE, + source INT NOT NULL REFERENCES actor.org_unit (id), + dest INT NOT NULL REFERENCES actor.org_unit (id), + persistant_transfer BOOL NOT NULL DEFAULT FALSE, + source_send_time TIMESTAMP WITH TIME ZONE, + dest_recv_time TIMESTAMP WITH TIME ZONE, + prev_hop INT REFERENCES action.transit_copy (id) +); + +CREATE TABLE action.hold_transit_copy ( + hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED +) INHERITS (action.transit_copy); + +CREATE TABLE action.unfulfilled_hold_list ( + id BIGSERIAL PRIMARY KEY, + hold INT NOT NULL, + current_copy BIGINT NOT NULL, + circ_lib INT NOT NULL, + fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() +); + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql new file mode 100644 index 0000000000..ac93024f2f --- /dev/null +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -0,0 +1,77 @@ +BEGIN; + +ALTER TABLE actor.usr ADD CONSTRAINT actor_usr_mailing_address_fkey FOREIGN KEY (mailing_address) REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE actor.usr ADD CONSTRAINT actor_usr_billining_address_fkey FOREIGN KEY (billing_address) REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE actor.usr ADD CONSTRAINT actor_usr_home_ou_fkey FOREIGN KEY (home_ou) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE actor.stat_cat ADD CONSTRAINT actor_stat_cat_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE actor.stat_cat_entry ADD CONSTRAINT actor_stat_cat_entry_stat_cat_fkey FOREIGN KEY (stat_cat) REFERENCES actor.stat_cat (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE actor.stat_cat_entry ADD CONSTRAINT actor_stat_cat_entry_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE actor.stat_cat_entry_usr_map ADD CONSTRAINT actor_sc_tu_fkey FOREIGN KEY (target_usr) REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + + +ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_mailing_address_fkey FOREIGN KEY (mailing_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_billing_address_fkey FOREIGN KEY (billing_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; +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 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; + +ALTER TABLE biblio.record_entry ADD CONSTRAINT biblio_record_entry_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE biblio.record_entry ADD CONSTRAINT biblio_record_entry_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.metarecord ADD CONSTRAINT metabib_metarecord_master_record_fkey FOREIGN KEY (master_record) REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.title_field_entry ADD CONSTRAINT metabib_title_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.title_field_entry ADD CONSTRAINT metabib_title_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.author_field_entry ADD CONSTRAINT metabib_author_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.author_field_entry ADD CONSTRAINT metabib_author_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.subject_field_entry ADD CONSTRAINT metabib_subject_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.subject_field_entry ADD CONSTRAINT metabib_subject_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.rec_descriptor ADD CONSTRAINT metabib_rec_descriptor_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.full_rec ADD CONSTRAINT metabib_full_rec_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_source_fkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_metarecord_fkey FOREIGN KEY (metarecord) REFERENCES metabib.metarecord (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_call_number_fkey FOREIGN KEY (call_number) REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_copy_fkey FOREIGN KEY (owning_copy) REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_owning_lib_fkey FOREIGN KEY (owning_lib) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE asset.call_number_note ADD CONSTRAINT asset_call_number_note_record_fkey FOREIGN KEY (call_number) REFERENCES asset.call_number (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.call_number_note ADD CONSTRAINT asset_call_number_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE asset.stat_cat ADD CONSTRAINT a_sc_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE asset.stat_cat_entry ADD CONSTRAINT a_sce_sc_fkey FOREIGN KEY (stat_cat) REFERENCES asset.stat_cat (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.stat_cat_entry ADD CONSTRAINT a_sce_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE asset.stat_cat_entry_copy_map ADD CONSTRAINT a_sc_oc_fkey FOREIGN KEY (owning_copy) REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.stat_cat_entry_copy_map ADD CONSTRAINT a_sc_sce_fkey FOREIGN KEY (stat_cat_entry) REFERENCES asset.stat_cat_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE money.billable_xact ADD CONSTRAINT money_billable_xact_usr_fkey FOREIGN KEY (usr) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_usr_fkey FOREIGN KEY (usr) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_circ_lib_fkey FOREIGN KEY (circ_lib) REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_target_copy_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/900.audit-tables.sql b/Open-ILS/src/sql/Pg/900.audit-tables.sql new file mode 100644 index 0000000000..f7e14e8e0f --- /dev/null +++ b/Open-ILS/src/sql/Pg/900.audit-tables.sql @@ -0,0 +1,42 @@ +DROP SCHEMA auditor CASCADE; + +BEGIN; + +CREATE SCHEMA auditor; + +CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$ +BEGIN + EXECUTE $$ + CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history ( + audit_time TIMESTAMP WITH TIME ZONE NOT NULL, + audit_action CHAR(1) NOT NULL, + LIKE $$ || sch || $$.$$ || tbl || $$ + ); + $$; + + EXECUTE $$ + CREATE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func () + RETURNS TRIGGER AS $func$ + BEGIN + INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history + SELECT now(), SUBSTR(TG_OP,1,1), OLD.*; + RETURN NULL; + END; + $func$ LANGUAGE 'plpgsql'; + $$; + + EXECUTE $$ + CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger + AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW + EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func (); + $$; + RETURN TRUE; +END; +$creator$ LANGUAGE 'plpgsql'; + +SELECT auditor.create_auditor ( 'actor', 'usr' ); +SELECT auditor.create_auditor ( 'biblio', 'record_entry' ); +SELECT auditor.create_auditor ( 'asset', 'copy' ); + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/build-db-Postgres.sh b/Open-ILS/src/sql/Pg/build-db-Postgres.sh new file mode 100755 index 0000000000..74a63f9072 --- /dev/null +++ b/Open-ILS/src/sql/Pg/build-db-Postgres.sh @@ -0,0 +1,13 @@ +#!/bin/sh + +psql -U $1 -d $2 -f 002.schema.config.sql +psql -U $1 -d $2 -f 005.schema.actors.sql +psql -U $1 -d $2 -f 010.schema.biblio.sql +psql -U $1 -d $2 -f 020.schema.functions.sql +psql -U $1 -d $2 -f 030.schema.metabib.sql +psql -U $1 -d $2 -f 040.schema.asset.sql +psql -U $1 -d $2 -f 080.schema.money.sql +psql -U $1 -d $2 -f 090.schema.action.sql + +psql -U $1 -d $2 -f 800.fkeys.sql +psql -U $1 -d $2 -f 900.audit-tables.sql -- 2.43.2