From 917535608161c2c190e933fb2abdba2543aefb4a Mon Sep 17 00:00:00 2001 From: miker Date: Sun, 24 Jul 2005 21:15:53 +0000 Subject: [PATCH] moving... arg git-svn-id: svn://svn.open-ils.org/ILS/trunk@1396 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Postgres/002.schema.config.sql | 417 --------------------- Open-ILS/src/sql/Postgres/005.schema.actors.sql | 389 ------------------- .../src/sql/Postgres/006.schema.permissions.sql | 216 ----------- Open-ILS/src/sql/Postgres/010.schema.biblio.sql | 43 --- Open-ILS/src/sql/Postgres/020.schema.functions.sql | 87 ----- Open-ILS/src/sql/Postgres/030.schema.metabib.sql | 128 ------- Open-ILS/src/sql/Postgres/040.schema.asset.sql | 135 ------- Open-ILS/src/sql/Postgres/080.schema.money.sql | 139 ------- Open-ILS/src/sql/Postgres/090.schema.action.sql | 164 -------- Open-ILS/src/sql/Postgres/800.fkeys.sql | 77 ---- Open-ILS/src/sql/Postgres/900.audit-tables.sql | 42 --- Open-ILS/src/sql/Postgres/build-db-Postgres.sh | 13 - 12 files changed, 1850 deletions(-) delete mode 100644 Open-ILS/src/sql/Postgres/002.schema.config.sql delete mode 100644 Open-ILS/src/sql/Postgres/005.schema.actors.sql delete mode 100644 Open-ILS/src/sql/Postgres/006.schema.permissions.sql delete mode 100644 Open-ILS/src/sql/Postgres/010.schema.biblio.sql delete mode 100644 Open-ILS/src/sql/Postgres/020.schema.functions.sql delete mode 100644 Open-ILS/src/sql/Postgres/030.schema.metabib.sql delete mode 100644 Open-ILS/src/sql/Postgres/040.schema.asset.sql delete mode 100644 Open-ILS/src/sql/Postgres/080.schema.money.sql delete mode 100644 Open-ILS/src/sql/Postgres/090.schema.action.sql delete mode 100644 Open-ILS/src/sql/Postgres/800.fkeys.sql delete mode 100644 Open-ILS/src/sql/Postgres/900.audit-tables.sql delete mode 100755 Open-ILS/src/sql/Postgres/build-db-Postgres.sh diff --git a/Open-ILS/src/sql/Postgres/002.schema.config.sql b/Open-ILS/src/sql/Postgres/002.schema.config.sql deleted file mode 100644 index 84dd426bc1..0000000000 --- a/Open-ILS/src/sql/Postgres/002.schema.config.sql +++ /dev/null @@ -1,417 +0,0 @@ - -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/Postgres/005.schema.actors.sql b/Open-ILS/src/sql/Postgres/005.schema.actors.sql deleted file mode 100644 index a28744db28..0000000000 --- a/Open-ILS/src/sql/Postgres/005.schema.actors.sql +++ /dev/null @@ -1,389 +0,0 @@ -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/Postgres/006.schema.permissions.sql b/Open-ILS/src/sql/Postgres/006.schema.permissions.sql deleted file mode 100644 index 2df0a0b9f7..0000000000 --- a/Open-ILS/src/sql/Postgres/006.schema.permissions.sql +++ /dev/null @@ -1,216 +0,0 @@ -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/Postgres/010.schema.biblio.sql b/Open-ILS/src/sql/Postgres/010.schema.biblio.sql deleted file mode 100644 index f844c3d898..0000000000 --- a/Open-ILS/src/sql/Postgres/010.schema.biblio.sql +++ /dev/null @@ -1,43 +0,0 @@ -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/Postgres/020.schema.functions.sql b/Open-ILS/src/sql/Postgres/020.schema.functions.sql deleted file mode 100644 index 059aa79c22..0000000000 --- a/Open-ILS/src/sql/Postgres/020.schema.functions.sql +++ /dev/null @@ -1,87 +0,0 @@ -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/Postgres/030.schema.metabib.sql b/Open-ILS/src/sql/Postgres/030.schema.metabib.sql deleted file mode 100644 index 32d61802f3..0000000000 --- a/Open-ILS/src/sql/Postgres/030.schema.metabib.sql +++ /dev/null @@ -1,128 +0,0 @@ -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/Postgres/040.schema.asset.sql b/Open-ILS/src/sql/Postgres/040.schema.asset.sql deleted file mode 100644 index 7e38df8be8..0000000000 --- a/Open-ILS/src/sql/Postgres/040.schema.asset.sql +++ /dev/null @@ -1,135 +0,0 @@ -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/Postgres/080.schema.money.sql b/Open-ILS/src/sql/Postgres/080.schema.money.sql deleted file mode 100644 index 42dec9d6f1..0000000000 --- a/Open-ILS/src/sql/Postgres/080.schema.money.sql +++ /dev/null @@ -1,139 +0,0 @@ -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/Postgres/090.schema.action.sql b/Open-ILS/src/sql/Postgres/090.schema.action.sql deleted file mode 100644 index 5bbfdc8cb6..0000000000 --- a/Open-ILS/src/sql/Postgres/090.schema.action.sql +++ /dev/null @@ -1,164 +0,0 @@ -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/Postgres/800.fkeys.sql b/Open-ILS/src/sql/Postgres/800.fkeys.sql deleted file mode 100644 index ac93024f2f..0000000000 --- a/Open-ILS/src/sql/Postgres/800.fkeys.sql +++ /dev/null @@ -1,77 +0,0 @@ -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/Postgres/900.audit-tables.sql b/Open-ILS/src/sql/Postgres/900.audit-tables.sql deleted file mode 100644 index f7e14e8e0f..0000000000 --- a/Open-ILS/src/sql/Postgres/900.audit-tables.sql +++ /dev/null @@ -1,42 +0,0 @@ -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/Postgres/build-db-Postgres.sh b/Open-ILS/src/sql/Postgres/build-db-Postgres.sh deleted file mode 100755 index 1bd2daf4d7..0000000000 --- a/Open-ILS/src/sql/Postgres/build-db-Postgres.sh +++ /dev/null @@ -1,13 +0,0 @@ -#!/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.11.0