-- 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 );
+ VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', 'System Account', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 );
CREATE TABLE actor.usr_setting (
* Copyright (C) 2005 Georgia Public Library Service
* Mike Rylander <mrylander@gmail.com>
*
- * User objects
+ * User settings
*
* This table contains any arbitrary settings that a client
* program would like to save for a user.
*/
$$;
+CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
+
+
CREATE TABLE actor.stat_cat (
id SERIAL PRIMARY KEY,
owner INT NOT NULL,
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');
+INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL1', 'Example Sub-lib 1');
+INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM1', 'Example Bookmobile 1');
+
+CREATE TABLE actor.org_unit_setting (
+ id BIGSERIAL PRIMARY KEY,
+ org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
+ name TEXT NOT NULL,
+ value TEXT NOT NULL,
+ CONSTRAINT name_once_per_value UNIQUE (org_unit,name)
+);
+COMMENT ON TABLE actor.org_unit_setting IS $$
+/*
+ * Copyright (C) 2005 Georgia Public Library Service
+ * Mike Rylander <mrylander@gmail.com>
+ *
+ * Org Unit settings
+ *
+ * This table contains any arbitrary settings that a client
+ * program would like to save for an org unit.
+ *
+ * ****
+ *
+ * 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_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
+
CREATE TABLE actor.usr_address (
id SERIAL PRIMARY KEY,
post_code TEXT NOT NULL
);
+CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
+
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));
post_code TEXT NOT NULL
);
+CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
+
INSERT INTO actor.org_address VALUES (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;
CREATE SCHEMA permission;
CREATE TABLE permission.perm_list (
- id SERIAL PRIMARY KEY,
- code TEXT NOT NULL UNIQUE
+ id SERIAL PRIMARY KEY,
+ code TEXT NOT NULL UNIQUE,
+ description TEXT
);
CREATE INDEX perm_list_code_idx ON permission.perm_list (code);
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
+ id SERIAL PRIMARY KEY,
+ name TEXT NOT NULL UNIQUE,
+ parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT,
+ description TEXT
);
CREATE INDEX grp_tree_parent ON permission.grp_tree (parent);
END;
$$ LANGUAGE PLPGSQL;
-CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT, target INT ) RETURNS BOOL AS $$
+CREATE OR REPLACE FUNCTION permission.usr_has_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
DECLARE
r_usr actor.usr%ROWTYPE;
r_perm permission.usr_perm_map%ROWTYPE;
SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
+ IF r_usr.active = FALSE THEN
+ RETURN FALSE;
+ END IF;
+
+ IF r_usr.super_user = TRUE THEN
+ RETURN TRUE;
+ END IF;
+
+
FOR r_perm IN SELECT *
FROM permission.usr_perms(iuser) p
JOIN permission.perm_list l
OR p.perm = -1 LOOP
PERFORM *
- FROM actor.org_unit_descendants(target,r_perm.depth)
+ FROM actor.org_unit_descendants(target_ou,r_perm.depth)
WHERE id = r_usr.home_ou;
IF FOUND THEN
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',
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 '
+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;
+$$ LANGUAGE SQL STABLE;
+
+CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
+ SELECT *
+ FROM actor.org_unit_ancestors($1)
+ UNION
+ SELECT *
+ FROM actor.org_unit_ancestors($2);
+$$ LANGUAGE SQL STABLE;
+CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
+ SELECT *
+ FROM actor.org_unit_ancestors($1)
+ INTERSECT
+ SELECT *
+ FROM actor.org_unit_ancestors($2);
+$$ LANGUAGE SQL STABLE;
-CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS '
+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
+ SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
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;
+ SELECT id FROM actor.org_unit_common_ancestors($1, $2)
+ ) z;
+$$ LANGUAGE SQL STABLE;
BEFORE UPDATE OR INSERT ON metabib.title_field_entry
FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
+CREATE INDEX metabib_title_field_entry_index_vector_idx ON (metabib.title_field_entry) USING GIST (index_vector);
+
CREATE TABLE metabib.author_field_entry (
id BIGSERIAL PRIMARY KEY,
BEFORE UPDATE OR INSERT ON metabib.author_field_entry
FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
+CREATE INDEX metabib_author_field_entry_index_vector_idx ON (metabib.author_field_entry) USING GIST (index_vector);
+
CREATE TABLE metabib.subject_field_entry (
id BIGSERIAL PRIMARY KEY,
BEFORE UPDATE OR INSERT ON metabib.subject_field_entry
FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
+CREATE INDEX metabib_subject_field_entry_index_vector_idx ON (metabib.subject_field_entry) USING GIST (index_vector);
+
CREATE TABLE metabib.keyword_field_entry (
id BIGSERIAL PRIMARY KEY,
BEFORE UPDATE OR INSERT ON metabib.keyword_field_entry
FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
+CREATE INDEX metabib_keyword_field_entry_index_vector_idx ON (metabib.keyword_field_entry) USING GIST (index_vector);
+
+
CREATE TABLE metabib.series_field_entry (
id BIGSERIAL PRIMARY KEY,
source BIGINT NOT NULL,
BEFORE UPDATE OR INSERT ON metabib.series_field_entry
FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
+CREATE INDEX metabib_series_field_entry_index_vector_idx ON (metabib.series_field_entry) USING GIST (index_vector);
+
+
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,
+ item_type "char",
+ item_form "char",
+ bib_level "char",
+ control_type "char",
+ char_encoding "char",
+ enc_level "char",
+ audience "char",
cat_form TEXT,
pub_status TEXT,
- item_lang TEXT,
- audience TEXT
+ item_lang TEXT
);
CREATE INDEX metabib_rec_descriptor_record_idx ON metabib.rec_descriptor (record);
/* We may not need these...
id BIGSERIAL PRIMARY KEY,
record BIGINT NOT NULL,
tag CHAR(3) NOT NULL,
- ind1 CHAR(1),
- ind2 CHAR(1),
- subfield CHAR(1),
+ ind1 "char",
+ ind2 "char",
+ subfield "char",
value TEXT NOT NULL,
index_vector tsvector NOT NULL
);
BEFORE UPDATE OR INSERT ON metabib.full_rec
FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
+CREATE INDEX metabib_full_rec_index_vector_idx ON (metabib.full_rec) USING GIST (index_vector);
+
+
CREATE TABLE metabib.metarecord_source_map (
id BIGSERIAL PRIMARY KEY,
metarecord BIGINT NOT NULL,
CREATE TABLE asset.copy_transparency (
id SERIAL PRIMARY KEY,
- name TEXT NOT NULL,
+ deposit_amount NUMERIC(6,2),
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) ),
+ holdable BOOL,
circulate BOOL,
deposit BOOL,
- deposit_amount NUMERIC(6,2),
ref BOOL,
+ opac_visible BOOL,
circ_modifier TEXT,
circ_as_type TEXT,
- opac_visible BOOL,
+ name TEXT NOT NULL,
CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
);
CREATE TABLE asset.stat_cat (
id SERIAL PRIMARY KEY,
owner INT NOT NULL,
+ opac_visible BOOL NOT NULL DEFAULT FALSE,
name TEXT NOT NULL,
- opac_visible BOOL NOT NULL DEFAULT FALSE,
CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
);
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,
+ label TEXT 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 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 DEFAULT FALSE
+ voided BOOL NOT NULL DEFAULT FALSE,
+ amount NUMERIC(6,2) NOT NULL,
+ note TEXT
);
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 DEFAULT FALSE
+ voided BOOL NOT NULL DEFAULT FALSE,
+ amount NUMERIC(6,2) NOT NULL,
+ note TEXT
);
CREATE INDEX m_p_xact_idx ON money.payment (xact);
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
+ required BOOL NOT NULL DEFAULT FALSE,
+ name TEXT NOT NULL,
+ description TEXT NOT NULL
);
CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
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
+ renewal BOOL NOT NULL DEFAULT FALSE,
+ due_date TIMESTAMP WITH TIME ZONE NOT NULL,
+ duration INTERVAL NOT NULL, -- derived from "circ duration" rule
+ fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "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,
+ duration_rule TEXT NOT NULL, -- name of "circ duration" rule
+ recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
+ max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
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;
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;
+ IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'LOST' THEN
+ UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
+ END IF;
+ IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'MISSING' THEN
+ UPDATE asset.copy SET status = 4 WHERE id = NEW.target_copy;
+ END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
return_time TIMESTAMP WITH TIME ZONE,
prev_check_time TIMESTAMP WITH TIME ZONE,
expire_time TIMESTAMP WITH TIME ZONE,
+ hold_type "char" NOT NULL CHECK (hold_type IN ('M','T','V','C')),
+ target BIGINT NOT NULL, -- see hold_type
+ current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL,
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
+ holdable_formats TEXT,
+ phone_notify TEXT,
+ email_notify TEXT
);
CREATE TABLE action.transit_copy (
id SERIAL PRIMARY KEY,
+ source_send_time TIMESTAMP WITH TIME ZONE,
+ dest_recv_time TIMESTAMP WITH TIME ZONE,
target_copy BIGINT NOT NULL REFERENCES 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)
+ prev_hop INT REFERENCES action.transit_copy (id),
+ persistant_transfer BOOL NOT NULL DEFAULT FALSE
);
CREATE TABLE action.hold_transit_copy (
- hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
+ 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,
+ id BIGSERIAL PRIMARY KEY,
current_copy BIGINT NOT NULL,
+ hold INT NOT NULL,
circ_lib INT NOT NULL,
fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
projects / working / Evergreen.git / commitdiff