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_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
92 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
94 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
96 NEW.passwd = MD5( NEW.passwd );
101 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
103 IF NEW.passwd <> OLD.passwd THEN
104 NEW.passwd = MD5( NEW.passwd );
110 CREATE TRIGGER actor_crypt_pw_update_trigger
111 BEFORE UPDATE ON actor.usr FOR EACH ROW
112 EXECUTE PROCEDURE actor.crypt_pw_update ();
114 CREATE TRIGGER actor_crypt_pw_insert_trigger
115 BEFORE INSERT ON actor.usr FOR EACH ROW
116 EXECUTE PROCEDURE actor.crypt_pw_insert ();
118 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;
120 CREATE TABLE actor.usr_note (
121 id BIGSERIAL PRIMARY KEY,
122 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
123 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
124 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
125 pub BOOL NOT NULL DEFAULT FALSE,
129 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
130 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
132 CREATE TABLE actor.usr_setting (
133 id BIGSERIAL PRIMARY KEY,
134 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
135 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
137 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
139 COMMENT ON TABLE actor.usr_setting IS $$
142 This table contains any arbitrary settings that a client
143 program would like to save for a user.
146 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
149 CREATE TABLE actor.stat_cat (
150 id SERIAL PRIMARY KEY,
153 opac_visible BOOL NOT NULL DEFAULT FALSE,
154 usr_summary BOOL NOT NULL DEFAULT FALSE,
155 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
157 COMMENT ON TABLE actor.stat_cat IS $$
158 User Statistical Catagories
160 Local data collected about Users is placed into a Statistical
161 Catagory. Here's where those catagories are defined.
165 CREATE TABLE actor.stat_cat_entry (
166 id SERIAL PRIMARY KEY,
167 stat_cat INT NOT NULL,
170 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
172 COMMENT ON TABLE actor.stat_cat_entry IS $$
173 User Statistical Catagory Entries
175 Local data collected about Users is placed into a Statistical
176 Catagory. Each library can create entries into any of its own
177 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
181 CREATE TABLE actor.stat_cat_entry_usr_map (
182 id BIGSERIAL PRIMARY KEY,
183 stat_cat_entry TEXT NOT NULL,
184 stat_cat INT NOT NULL,
185 target_usr INT NOT NULL,
186 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
188 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
189 Statistical Catagory Entry to User map
191 Records the stat_cat entries for each user.
194 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
196 CREATE TABLE actor.card (
197 id SERIAL PRIMARY KEY,
198 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
199 barcode TEXT NOT NULL UNIQUE,
200 active BOOL NOT NULL DEFAULT TRUE
202 COMMENT ON TABLE actor.card IS $$
205 Each User has one or more library cards. The current "main"
206 card is linked to here from the actor.usr table, and it is up
207 to the consortium policy whether more than one card can be
208 active for any one user at a given time.
211 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
212 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
214 CREATE TABLE actor.org_unit_type (
215 id SERIAL PRIMARY KEY,
217 opac_label TEXT NOT NULL,
219 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
220 can_have_vols BOOL NOT NULL DEFAULT TRUE,
221 can_have_users BOOL NOT NULL DEFAULT TRUE
223 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
225 CREATE TABLE actor.org_unit (
226 id SERIAL PRIMARY KEY,
227 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
228 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
233 shortname TEXT NOT NULL UNIQUE,
234 name TEXT NOT NULL UNIQUE,
237 opac_visible BOOL NOT NULL DEFAULT TRUE,
238 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
240 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
241 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
242 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
243 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
244 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
245 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
247 CREATE TABLE actor.org_lasso (
248 id SERIAL PRIMARY KEY,
252 CREATE TABLE actor.org_lasso_map (
253 id SERIAL PRIMARY KEY,
254 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
255 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
257 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
258 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
260 CREATE TABLE actor.org_unit_proximity (
261 id BIGSERIAL PRIMARY KEY,
266 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
268 CREATE TABLE actor.hours_of_operation (
269 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
270 dow_0_open TIME NOT NULL DEFAULT '09:00',
271 dow_0_close TIME NOT NULL DEFAULT '17:00',
272 dow_1_open TIME NOT NULL DEFAULT '09:00',
273 dow_1_close TIME NOT NULL DEFAULT '17:00',
274 dow_2_open TIME NOT NULL DEFAULT '09:00',
275 dow_2_close TIME NOT NULL DEFAULT '17:00',
276 dow_3_open TIME NOT NULL DEFAULT '09:00',
277 dow_3_close TIME NOT NULL DEFAULT '17:00',
278 dow_4_open TIME NOT NULL DEFAULT '09:00',
279 dow_4_close TIME NOT NULL DEFAULT '17:00',
280 dow_5_open TIME NOT NULL DEFAULT '09:00',
281 dow_5_close TIME NOT NULL DEFAULT '17:00',
282 dow_6_open TIME NOT NULL DEFAULT '09:00',
283 dow_6_close TIME NOT NULL DEFAULT '17:00'
285 COMMENT ON TABLE actor.hours_of_operation IS $$
286 When does this org_unit usually open and close? (Variations
287 are expressed in the actor.org_unit_closed table.)
289 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
290 When does this org_unit open on Monday?
292 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
293 When does this org_unit close on Monday?
295 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
296 When does this org_unit open on Tuesday?
298 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
299 When does this org_unit close on Tuesday?
301 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
302 When does this org_unit open on Wednesday?
304 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
305 When does this org_unit close on Wednesday?
307 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
308 When does this org_unit open on Thursday?
310 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
311 When does this org_unit close on Thursday?
313 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
314 When does this org_unit open on Friday?
316 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
317 When does this org_unit close on Friday?
319 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
320 When does this org_unit open on Saturday?
322 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
323 When does this org_unit close on Saturday?
325 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
326 When does this org_unit open on Sunday?
328 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
329 When does this org_unit close on Sunday?
332 CREATE TABLE actor.org_unit_closed (
333 id SERIAL PRIMARY KEY,
334 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
335 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
336 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
340 -- Workstation registration...
341 CREATE TABLE actor.workstation (
342 id SERIAL PRIMARY KEY,
343 name TEXT NOT NULL UNIQUE,
344 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
347 CREATE TABLE actor.usr_org_unit_opt_in (
348 id SERIAL PRIMARY KEY,
349 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
350 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
351 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
352 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
353 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
354 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
356 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
358 CREATE TABLE actor.org_unit_setting (
359 id BIGSERIAL PRIMARY KEY,
360 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
361 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
363 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
365 COMMENT ON TABLE actor.org_unit_setting IS $$
368 This table contains any arbitrary settings that a client
369 program would like to save for an org unit.
372 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
375 CREATE TABLE actor.usr_address (
376 id SERIAL PRIMARY KEY,
377 valid BOOL NOT NULL DEFAULT TRUE,
378 within_city_limits BOOL NOT NULL DEFAULT TRUE,
379 address_type TEXT NOT NULL DEFAULT 'MAILING',
380 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
381 street1 TEXT NOT NULL,
386 country TEXT NOT NULL,
387 post_code TEXT NOT NULL,
388 pending BOOL NOT NULL DEFAULT FALSE,
389 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
392 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
394 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
395 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
397 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
398 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
399 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
401 CREATE TABLE actor.usr_password_reset (
402 id SERIAL PRIMARY KEY,
404 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
405 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
406 has_been_reset BOOL NOT NULL DEFAULT false
408 COMMENT ON TABLE actor.usr_password_reset IS $$
409 Self-serve password reset requests
411 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
412 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
413 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
414 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
416 CREATE TABLE actor.org_address (
417 id SERIAL PRIMARY KEY,
418 valid BOOL NOT NULL DEFAULT TRUE,
419 address_type TEXT NOT NULL DEFAULT 'MAILING',
420 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
421 street1 TEXT NOT NULL,
426 country TEXT NOT NULL,
427 post_code TEXT NOT NULL,
431 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
433 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
434 SELECT SUBSTRING( $1, 1, 5);
437 CREATE TABLE actor.usr_standing_penalty (
438 id SERIAL PRIMARY KEY,
439 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
440 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
441 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
442 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
443 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
444 stop_date TIMESTAMP WITH TIME ZONE,
447 COMMENT ON TABLE actor.usr_standing_penalty IS $$
448 User standing penalties
451 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
452 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
455 CREATE TABLE actor.usr_saved_search (
456 id SERIAL PRIMARY KEY,
457 owner INT NOT NULL REFERENCES actor.usr (id)
459 DEFERRABLE INITIALLY DEFERRED,
461 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
462 query_text TEXT NOT NULL,
463 query_type TEXT NOT NULL
464 CONSTRAINT valid_query_text CHECK (
465 query_type IN ( 'URL' )) DEFAULT 'URL',
466 -- we may add other types someday
468 CONSTRAINT valid_target CHECK (
469 target IN ( 'record', 'metarecord', 'callnumber' )),
470 CONSTRAINT name_once_per_user UNIQUE (owner, name)