From 00b08b419552658a31f7e9d64ff28b4597f9ede1 Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 22 Jul 2005 19:59:07 +0000 Subject: [PATCH] make everything generic for Alpha; create some reasonable examples/defaults git-svn-id: svn://svn.open-ils.org/ILS/trunk@1362 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../src/sql/Postgres/002.schema.config.sql | 89 +++++--------- .../src/sql/Postgres/005.schema.actors.sql | 26 ++-- .../sql/Postgres/006.schema.permissions.sql | 114 ++++++++++++++---- .../src/sql/Postgres/080.schema.money.sql | 13 +- 4 files changed, 135 insertions(+), 107 deletions(-) diff --git a/Open-ILS/src/sql/Postgres/002.schema.config.sql b/Open-ILS/src/sql/Postgres/002.schema.config.sql index 8679d02e1d..9ca88a5fd1 100644 --- a/Open-ILS/src/sql/Postgres/002.schema.config.sql +++ b/Open-ILS/src/sql/Postgres/002.schema.config.sql @@ -89,10 +89,11 @@ COMMENT ON TABLE config.standing IS $$ */ $$; - 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')), @@ -139,7 +140,7 @@ INSERT INTO config.metabib_field ( field_class, name, xpath ) VALUES ( 'subject' 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 ( '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 ( @@ -210,6 +211,8 @@ COMMENT ON TABLE config.rule_circ_duration IS $$ */ $$; +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, @@ -241,6 +244,8 @@ COMMENT ON TABLE config.rule_max_fine IS $$ */ $$; +INSERT INTO rule_max_fine VALUES (DEFAULT, 'books', 50.00); + CREATE TABLE config.rule_recuring_fine ( id SERIAL PRIMARY KEY, @@ -277,6 +282,8 @@ COMMENT ON TABLE config.rule_recuring_fine IS $$ */ $$; +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, @@ -312,11 +319,14 @@ COMMENT ON TABLE config.rule_age_hold_protect IS $$ */ $$; +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 'F' + holdable BOOL NOT NULL DEFAULT FALSE ); COMMENT ON TABLE config.copy_status IS $$ /* @@ -353,20 +363,20 @@ COMMENT ON TABLE config.copy_status IS $$ */ $$; -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) VALUES ('In transit'); -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'); +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) VALUES ('In transit'); +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); @@ -404,49 +414,4 @@ INSERT INTO config.net_access_level (name) VALUES ('Full'); INSERT INTO config.net_access_level (name) VALUES ('None'); - -/* - -CREATE TABLE config.new_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, - search_xpath TEXT NOT NULL, - browse_xpath TEXT -); - -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath, browse_xpath ) - VALUES ( 'series', 'seriestitle', $$//mods/relatedItem[@type="series"]/titleInfo$$, $$//mods/relatedItem[@type="series"]/titleInfo/title$$ ); - -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath, browse_xpath ) - VALUES ( 'title', 'abbreviated', $$//mods/titleInfo[title and (@type='abreviated')]$$, $$//mods/titleInfo[title and (@type='abreviated')]/title$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath, browse_xpath ) - VALUES ( 'title', 'translated', $$//mods/titleInfo[title and (@type='translated')]$$, $$//mods/titleInfo[title and (@type='translated')]/title$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath, browse_xpath ) - VALUES ( 'title', 'uniform', $$//mods/titleInfo[title and (@type='uniform')]$$, $$//mods/titleInfo[title and (@type='uniform')]/title$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath, browse_xpath ) - VALUES ( 'title', 'proper', $$//mods/titleInfo[title and not (@type)]$$, $$//mods/titleInfo[title and not (@type)]/title$$ ); - -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath ) - VALUES ( 'author', 'corporate', $$//mods/name[@type='corporate']/namePart[../role/text[text()='creator']]$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath ) - VALUES ( 'author', 'personal', $$//mods/name[@type='personal']/namePart[../role/text[text()='creator']]$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath ) - VALUES ( 'author', 'conference', $$//mods/name[@type='conference']/namePart[../role/text[text()='creator']]$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath ) - VALUES ( 'author', 'other', $$//mods/name[@type='personal']/namePart[not(../role)]$$ ); - -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath ) - VALUES ( 'subject', 'geographic', $$//mods/subject/geographic$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath ) - VALUES ( 'subject', 'name', $$//mods/subject/name$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath ) - VALUES ( 'subject', 'temporal', $$//mods/subject/temporal$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath ) - VALUES ( 'subject', 'topic', $$//mods/subject/topic$$ ); -INSERT INTO config.new_metabib_field ( field_class, name, search_xpath ) - VALUES ( 'keyword', 'keyword', $$//mods/*[not(local-name()='originInfo')]$$ ); - -*/ - COMMIT; diff --git a/Open-ILS/src/sql/Postgres/005.schema.actors.sql b/Open-ILS/src/sql/Postgres/005.schema.actors.sql index f29018acf9..83f6226738 100644 --- a/Open-ILS/src/sql/Postgres/005.schema.actors.sql +++ b/Open-ILS/src/sql/Postgres/005.schema.actors.sql @@ -49,7 +49,7 @@ CREATE TABLE actor.usr ( other_phone TEXT, mailing_address INT, billing_address INT, - home_ou INT, + home_ou INT NOT NULL, dob DATE NOT NULL, active BOOL NOT NULL DEFAULT TRUE, master_account BOOL NOT NULL DEFAULT FALSE, @@ -129,12 +129,8 @@ CREATE TRIGGER actor_crypt_pw_insert_trigger 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 ) - VALUES ( 3, 1,'admin', 'open-ils', 'Administrator', '', '1979-01-22', TRUE, TRUE, 1, 'identification' ); -INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value ) - VALUES ( 2, 2,'demo', 'demo', 'demo', 'user', '1979-01-22', FALSE, TRUE, 1, 'identification' ); -INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value ) - VALUES ( 1, 3,'athens', 'athens', 'athens', 'user', '1979-01-22', FALSE, TRUE, 1, 'identification' ); +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 ( @@ -303,8 +299,6 @@ $$; CREATE INDEX actor_card_usr_idx ON actor.card (usr); INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101'); -INSERT INTO actor.card (usr, barcode) VALUES (2,'101010101010102'); -INSERT INTO actor.card (usr, barcode) VALUES (3,'101010101010103'); CREATE TABLE actor.org_unit_type ( @@ -319,7 +313,7 @@ CREATE TABLE actor.org_unit_type ( 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','All of PINES', 0, NULL, FALSE, FALSE ); +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 ); @@ -343,7 +337,15 @@ CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_addre 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, 'PINES', 'Georgia PINES Consortium'); +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, @@ -381,5 +383,7 @@ CREATE TABLE actor.org_address ( 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 index f4f82f2ac9..2df0a0b9f7 100644 --- a/Open-ILS/src/sql/Postgres/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Postgres/006.schema.permissions.sql @@ -8,8 +8,43 @@ CREATE TABLE permission.perm_list ( code TEXT NOT NULL UNIQUE ); CREATE INDEX perm_list_code_idx ON permission.perm_list (code); -INSERT INTO permission.perm_list VALUES (DEFAULT,'EVERYTHING'); -INSERT INTO permission.perm_list VALUES (DEFAULT,'OPAC_LOGIN'); + +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, @@ -17,47 +52,73 @@ CREATE TABLE permission.grp_tree ( parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT ); CREATE INDEX grp_tree_parent ON permission.grp_tree (parent); -INSERT INTO permission.grp_tree VALUES (DEFAULT,'Users'); -INSERT INTO permission.grp_tree VALUES (DEFAULT,'Admin',1); + +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), - perm INT NOT NULL REFERENCES permission.perm_list (id), + 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 (DEFAULT,1,2,0); -INSERT INTO permission.grp_perm_map VALUES (DEFAULT,2,1,0); + +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), - perm INT NOT NULL REFERENCES permission.perm_list (id), + 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), - grp INT NOT NULL REFERENCES permission.grp_tree (id), + 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) ); -INSERT INTO permission.usr_grp_map (usr,grp) - SELECT id, (SELECT id FROM permission.grp_tree WHERE parent IS NULL LIMIT 1) FROM actor.usr; - -INSERT INTO permission.usr_grp_map (usr,grp) - SELECT 1, id FROM permission.grp_tree WHERE name = 'Admin'; - -CREATE OR REPLACE RULE add_usr_to_group AS - ON INSERT TO actor.usr DO ALSO - INSERT INTO permission.usr_grp_map (usr, grp) VALUES ( - NEW.id, - (SELECT id FROM permission.grp_tree WHERE parent IS NULL LIMIT 1) - ); - +-- 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.* @@ -133,7 +194,8 @@ BEGIN FROM permission.usr_perms(iuser) p JOIN permission.perm_list l ON (l.id = p.perm) - WHERE l.code = tperm LOOP + WHERE l.code = tperm + OR p.perm = -1 LOOP PERFORM * FROM actor.org_unit_descendants(target,r_perm.depth) diff --git a/Open-ILS/src/sql/Postgres/080.schema.money.sql b/Open-ILS/src/sql/Postgres/080.schema.money.sql index 0f92341a98..42dec9d6f1 100644 --- a/Open-ILS/src/sql/Postgres/080.schema.money.sql +++ b/Open-ILS/src/sql/Postgres/080.schema.money.sql @@ -70,14 +70,11 @@ CREATE OR REPLACE VIEW money.billable_xact_summary AS 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, - pg_class p, - money.transaction_billing_summary debit, - money.transaction_payment_summary credit - WHERE xact.xact_finish IS NULL - AND xact.tableoid = p.oid - AND xact.id = debit.xact - AND xact.id = credit.xact; + 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, -- 2.43.2