2 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
3 * Mike Rylander <mrylander@gmail.com>
4 * Copyright (C) 2010 Laurentian University
5 * Dan Scott <dscott@laurentian.ca>
7 * This program is free software; you can redistribute it and/or
8 * modify it under the terms of the GNU General Public License
9 * as published by the Free Software Foundation; either version 2
10 * of the License, or (at your option) any later version.
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS actor CASCADE;
22 COMMENT ON SCHEMA actor IS $$
23 Holds all tables pertaining to users and libraries (org units).
26 CREATE TABLE actor.usr (
27 id SERIAL PRIMARY KEY,
28 card INT UNIQUE, -- active card
29 profile INT NOT NULL, -- patron profile
30 usrname TEXT NOT NULL UNIQUE,
33 standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id) DEFERRABLE INITIALLY DEFERRED,
34 ident_type INT NOT NULL REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
36 ident_type2 INT REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
38 net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id) DEFERRABLE INITIALLY DEFERRED,
41 first_given_name TEXT NOT NULL,
42 second_given_name TEXT,
43 family_name TEXT NOT NULL,
52 dob TIMESTAMP WITH TIME ZONE,
53 active BOOL NOT NULL DEFAULT TRUE,
54 master_account BOOL NOT NULL DEFAULT FALSE,
55 super_user BOOL NOT NULL DEFAULT FALSE,
56 barred BOOL NOT NULL DEFAULT FALSE,
57 deleted BOOL NOT NULL DEFAULT FALSE,
58 juvenile BOOL NOT NULL DEFAULT FALSE,
59 usrgroup SERIAL NOT NULL,
60 claims_returned_count INT NOT NULL DEFAULT 0,
61 credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00,
62 last_xact_id TEXT NOT NULL DEFAULT 'none',
64 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
65 expire_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + '3 years'::INTERVAL),
66 claims_never_checked_out_count INT NOT NULL DEFAULT 0
68 COMMENT ON TABLE actor.usr IS $$
71 This table contains the core User objects that describe both
72 staff members and patrons. The difference between the two
73 types of users is based on the user's permissions.
76 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
77 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
78 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
79 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
81 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
82 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
83 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
85 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
87 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
88 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
89 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
91 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
92 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
94 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
95 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
97 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
98 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
100 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
101 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
103 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
105 NEW.passwd = MD5( NEW.passwd );
110 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
112 IF NEW.passwd <> OLD.passwd THEN
113 NEW.passwd = MD5( NEW.passwd );
119 CREATE TRIGGER actor_crypt_pw_update_trigger
120 BEFORE UPDATE ON actor.usr FOR EACH ROW
121 EXECUTE PROCEDURE actor.crypt_pw_update ();
123 CREATE TRIGGER actor_crypt_pw_insert_trigger
124 BEFORE INSERT ON actor.usr FOR EACH ROW
125 EXECUTE PROCEDURE actor.crypt_pw_insert ();
127 CREATE RULE protect_user_delete AS ON DELETE TO actor.usr DO INSTEAD UPDATE actor.usr SET deleted = TRUE WHERE OLD.id = actor.usr.id;
129 CREATE TABLE actor.usr_note (
130 id BIGSERIAL PRIMARY KEY,
131 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
132 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
133 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
134 pub BOOL NOT NULL DEFAULT FALSE,
138 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
139 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
141 CREATE TABLE actor.usr_setting (
142 id BIGSERIAL PRIMARY KEY,
143 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
144 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
146 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
148 COMMENT ON TABLE actor.usr_setting IS $$
151 This table contains any arbitrary settings that a client
152 program would like to save for a user.
155 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
158 CREATE TABLE actor.stat_cat (
159 id SERIAL PRIMARY KEY,
162 opac_visible BOOL NOT NULL DEFAULT FALSE,
163 usr_summary BOOL NOT NULL DEFAULT FALSE,
164 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
166 COMMENT ON TABLE actor.stat_cat IS $$
167 User Statistical Catagories
169 Local data collected about Users is placed into a Statistical
170 Catagory. Here's where those catagories are defined.
174 CREATE TABLE actor.stat_cat_entry (
175 id SERIAL PRIMARY KEY,
176 stat_cat INT NOT NULL,
179 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
181 COMMENT ON TABLE actor.stat_cat_entry IS $$
182 User Statistical Catagory Entries
184 Local data collected about Users is placed into a Statistical
185 Catagory. Each library can create entries into any of its own
186 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
190 CREATE TABLE actor.stat_cat_entry_usr_map (
191 id BIGSERIAL PRIMARY KEY,
192 stat_cat_entry TEXT NOT NULL,
193 stat_cat INT NOT NULL,
194 target_usr INT NOT NULL,
195 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
197 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
198 Statistical Catagory Entry to User map
200 Records the stat_cat entries for each user.
203 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
205 CREATE TABLE actor.card (
206 id SERIAL PRIMARY KEY,
207 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
208 barcode TEXT NOT NULL UNIQUE,
209 active BOOL NOT NULL DEFAULT TRUE
211 COMMENT ON TABLE actor.card IS $$
214 Each User has one or more library cards. The current "main"
215 card is linked to here from the actor.usr table, and it is up
216 to the consortium policy whether more than one card can be
217 active for any one user at a given time.
220 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
221 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
223 CREATE TABLE actor.org_unit_type (
224 id SERIAL PRIMARY KEY,
226 opac_label TEXT NOT NULL,
228 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
229 can_have_vols BOOL NOT NULL DEFAULT TRUE,
230 can_have_users BOOL NOT NULL DEFAULT TRUE
232 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
234 CREATE TABLE actor.org_unit (
235 id SERIAL PRIMARY KEY,
236 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
237 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
242 shortname TEXT NOT NULL UNIQUE,
243 name TEXT NOT NULL UNIQUE,
246 opac_visible BOOL NOT NULL DEFAULT TRUE,
247 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
249 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
250 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
251 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
252 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
253 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
254 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
256 CREATE TABLE actor.org_lasso (
257 id SERIAL PRIMARY KEY,
261 CREATE TABLE actor.org_lasso_map (
262 id SERIAL PRIMARY KEY,
263 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
264 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
266 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
267 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
269 CREATE TABLE actor.org_unit_proximity (
270 id BIGSERIAL PRIMARY KEY,
275 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
277 CREATE TABLE actor.hours_of_operation (
278 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
279 dow_0_open TIME NOT NULL DEFAULT '09:00',
280 dow_0_close TIME NOT NULL DEFAULT '17:00',
281 dow_1_open TIME NOT NULL DEFAULT '09:00',
282 dow_1_close TIME NOT NULL DEFAULT '17:00',
283 dow_2_open TIME NOT NULL DEFAULT '09:00',
284 dow_2_close TIME NOT NULL DEFAULT '17:00',
285 dow_3_open TIME NOT NULL DEFAULT '09:00',
286 dow_3_close TIME NOT NULL DEFAULT '17:00',
287 dow_4_open TIME NOT NULL DEFAULT '09:00',
288 dow_4_close TIME NOT NULL DEFAULT '17:00',
289 dow_5_open TIME NOT NULL DEFAULT '09:00',
290 dow_5_close TIME NOT NULL DEFAULT '17:00',
291 dow_6_open TIME NOT NULL DEFAULT '09:00',
292 dow_6_close TIME NOT NULL DEFAULT '17:00'
294 COMMENT ON TABLE actor.hours_of_operation IS $$
295 When does this org_unit usually open and close? (Variations
296 are expressed in the actor.org_unit_closed table.)
298 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
299 When does this org_unit open on Monday?
301 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
302 When does this org_unit close on Monday?
304 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
305 When does this org_unit open on Tuesday?
307 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
308 When does this org_unit close on Tuesday?
310 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
311 When does this org_unit open on Wednesday?
313 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
314 When does this org_unit close on Wednesday?
316 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
317 When does this org_unit open on Thursday?
319 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
320 When does this org_unit close on Thursday?
322 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
323 When does this org_unit open on Friday?
325 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
326 When does this org_unit close on Friday?
328 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
329 When does this org_unit open on Saturday?
331 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
332 When does this org_unit close on Saturday?
334 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
335 When does this org_unit open on Sunday?
337 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
338 When does this org_unit close on Sunday?
341 CREATE TABLE actor.org_unit_closed (
342 id SERIAL PRIMARY KEY,
343 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
344 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
345 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
349 -- Workstation registration...
350 CREATE TABLE actor.workstation (
351 id SERIAL PRIMARY KEY,
352 name TEXT NOT NULL UNIQUE,
353 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
356 CREATE TABLE actor.usr_org_unit_opt_in (
357 id SERIAL PRIMARY KEY,
358 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
359 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
360 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
361 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
362 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
363 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
365 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
367 CREATE TABLE actor.org_unit_setting (
368 id BIGSERIAL PRIMARY KEY,
369 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
370 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
372 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
374 COMMENT ON TABLE actor.org_unit_setting IS $$
377 This table contains any arbitrary settings that a client
378 program would like to save for an org unit.
381 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
384 CREATE TABLE actor.usr_address (
385 id SERIAL PRIMARY KEY,
386 valid BOOL NOT NULL DEFAULT TRUE,
387 within_city_limits BOOL NOT NULL DEFAULT TRUE,
388 address_type TEXT NOT NULL DEFAULT 'MAILING',
389 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
390 street1 TEXT NOT NULL,
395 country TEXT NOT NULL,
396 post_code TEXT NOT NULL,
397 pending BOOL NOT NULL DEFAULT FALSE,
398 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
401 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
403 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
404 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
406 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
407 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
408 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
410 CREATE TABLE actor.usr_password_reset (
411 id SERIAL PRIMARY KEY,
413 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
414 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
415 has_been_reset BOOL NOT NULL DEFAULT false
417 COMMENT ON TABLE actor.usr_password_reset IS $$
418 Self-serve password reset requests
420 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
421 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
422 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
423 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
425 CREATE TABLE actor.org_address (
426 id SERIAL PRIMARY KEY,
427 valid BOOL NOT NULL DEFAULT TRUE,
428 address_type TEXT NOT NULL DEFAULT 'MAILING',
429 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
430 street1 TEXT NOT NULL,
435 country TEXT NOT NULL,
436 post_code TEXT NOT NULL,
440 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
442 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
443 SELECT SUBSTRING( $1, 1, 5);
446 CREATE TABLE actor.usr_standing_penalty (
447 id SERIAL PRIMARY KEY,
448 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
449 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
450 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
451 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
452 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
453 stop_date TIMESTAMP WITH TIME ZONE,
456 COMMENT ON TABLE actor.usr_standing_penalty IS $$
457 User standing penalties
460 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
461 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
464 CREATE TABLE actor.usr_saved_search (
465 id SERIAL PRIMARY KEY,
466 owner INT NOT NULL REFERENCES actor.usr (id)
468 DEFERRABLE INITIALLY DEFERRED,
470 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
471 query_text TEXT NOT NULL,
472 query_type TEXT NOT NULL
473 CONSTRAINT valid_query_text CHECK (
474 query_type IN ( 'URL' )) DEFAULT 'URL',
475 -- we may add other types someday
477 CONSTRAINT valid_target CHECK (
478 target IN ( 'record', 'metarecord', 'callnumber' )),
479 CONSTRAINT name_once_per_user UNIQUE (owner, name)