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,
67 last_update_time TIMESTAMP WITH TIME ZONE
69 COMMENT ON TABLE actor.usr IS $$
72 This table contains the core User objects that describe both
73 staff members and patrons. The difference between the two
74 types of users is based on the user's permissions.
77 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
78 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
79 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
80 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
82 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
83 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
84 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
86 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
88 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
89 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
90 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
92 CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE
93 (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g')));
95 CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE
96 (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g')));
98 CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE
99 (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g')));
101 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
102 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
104 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
106 NEW.passwd = MD5( NEW.passwd );
111 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
113 IF NEW.passwd <> OLD.passwd THEN
114 NEW.passwd = MD5( NEW.passwd );
120 CREATE OR REPLACE FUNCTION actor.au_updated()
121 RETURNS TRIGGER AS $$
123 NEW.last_update_time := now();
128 CREATE TRIGGER au_update_trig
129 BEFORE UPDATE ON actor.usr
130 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
132 CREATE TRIGGER actor_crypt_pw_update_trigger
133 BEFORE UPDATE ON actor.usr FOR EACH ROW
134 EXECUTE PROCEDURE actor.crypt_pw_update ();
136 CREATE TRIGGER actor_crypt_pw_insert_trigger
137 BEFORE INSERT ON actor.usr FOR EACH ROW
138 EXECUTE PROCEDURE actor.crypt_pw_insert ();
140 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;
142 CREATE TABLE actor.usr_note (
143 id BIGSERIAL PRIMARY KEY,
144 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
145 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
146 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
147 pub BOOL NOT NULL DEFAULT FALSE,
151 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
152 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
154 CREATE TABLE actor.usr_setting (
155 id BIGSERIAL PRIMARY KEY,
156 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
157 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
159 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
161 COMMENT ON TABLE actor.usr_setting IS $$
164 This table contains any arbitrary settings that a client
165 program would like to save for a user.
168 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
170 CREATE TABLE actor.stat_cat_sip_fields (
171 field CHAR(2) PRIMARY KEY,
173 one_only BOOL NOT NULL DEFAULT FALSE
175 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
176 Actor Statistical Category SIP Fields
178 Contains the list of valid SIP Field identifiers for
179 Statistical Categories.
182 CREATE TABLE actor.stat_cat (
183 id SERIAL PRIMARY KEY,
186 opac_visible BOOL NOT NULL DEFAULT FALSE,
187 usr_summary BOOL NOT NULL DEFAULT FALSE,
188 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
190 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
192 COMMENT ON TABLE actor.stat_cat IS $$
193 User Statistical Catagories
195 Local data collected about Users is placed into a Statistical
196 Catagory. Here's where those catagories are defined.
200 CREATE TABLE actor.stat_cat_entry (
201 id SERIAL PRIMARY KEY,
202 stat_cat INT NOT NULL,
205 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
207 COMMENT ON TABLE actor.stat_cat_entry IS $$
208 User Statistical Catagory Entries
210 Local data collected about Users is placed into a Statistical
211 Catagory. Each library can create entries into any of its own
212 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
216 CREATE TABLE actor.stat_cat_entry_usr_map (
217 id BIGSERIAL PRIMARY KEY,
218 stat_cat_entry TEXT NOT NULL,
219 stat_cat INT NOT NULL,
220 target_usr INT NOT NULL,
221 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
223 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
224 Statistical Catagory Entry to User map
226 Records the stat_cat entries for each user.
229 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
231 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
233 sipfield actor.stat_cat_sip_fields%ROWTYPE;
236 IF NEW.sip_field IS NOT NULL THEN
237 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
238 IF sipfield.one_only THEN
239 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
240 IF use_count > 0 THEN
241 RAISE EXCEPTION 'Sip field cannot be used twice';
247 $func$ LANGUAGE PLPGSQL;
249 CREATE TRIGGER actor_stat_cat_sip_update_trigger
250 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
251 EXECUTE PROCEDURE actor.stat_cat_check();
253 CREATE TABLE actor.card (
254 id SERIAL PRIMARY KEY,
255 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
256 barcode TEXT NOT NULL UNIQUE,
257 active BOOL NOT NULL DEFAULT TRUE
259 COMMENT ON TABLE actor.card IS $$
262 Each User has one or more library cards. The current "main"
263 card is linked to here from the actor.usr table, and it is up
264 to the consortium policy whether more than one card can be
265 active for any one user at a given time.
268 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
269 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
271 CREATE TABLE actor.org_unit_type (
272 id SERIAL PRIMARY KEY,
274 opac_label TEXT NOT NULL,
276 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
277 can_have_vols BOOL NOT NULL DEFAULT TRUE,
278 can_have_users BOOL NOT NULL DEFAULT TRUE
280 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
282 CREATE TABLE actor.org_unit (
283 id SERIAL PRIMARY KEY,
284 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
285 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
290 shortname TEXT NOT NULL UNIQUE,
291 name TEXT NOT NULL UNIQUE,
294 opac_visible BOOL NOT NULL DEFAULT TRUE,
295 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
297 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
298 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
299 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
300 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
301 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
302 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
304 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
306 current_aou actor.org_unit%ROWTYPE;
312 seen_ous := ARRAY[NEW.id];
314 IF (TG_OP = 'UPDATE') THEN
315 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
316 RETURN NEW; -- Doing an UPDATE with no change, just return it
321 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
322 RETURN NEW; -- No loop. Carry on.
324 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
325 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
328 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
329 seen_ous := seen_ous || current_aou.id;
330 depth_count := depth_count + 1;
331 IF depth_count = 100 THEN
332 RAISE 'OU CHECK TOO DEEP';
340 CREATE TRIGGER actor_org_unit_parent_protect_trigger
341 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
342 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
344 CREATE TABLE actor.org_lasso (
345 id SERIAL PRIMARY KEY,
349 CREATE TABLE actor.org_lasso_map (
350 id SERIAL PRIMARY KEY,
351 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
352 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
354 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
355 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
357 CREATE TABLE actor.org_unit_proximity (
358 id BIGSERIAL PRIMARY KEY,
363 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
365 CREATE TABLE actor.hours_of_operation (
366 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
367 dow_0_open TIME NOT NULL DEFAULT '09:00',
368 dow_0_close TIME NOT NULL DEFAULT '17:00',
369 dow_1_open TIME NOT NULL DEFAULT '09:00',
370 dow_1_close TIME NOT NULL DEFAULT '17:00',
371 dow_2_open TIME NOT NULL DEFAULT '09:00',
372 dow_2_close TIME NOT NULL DEFAULT '17:00',
373 dow_3_open TIME NOT NULL DEFAULT '09:00',
374 dow_3_close TIME NOT NULL DEFAULT '17:00',
375 dow_4_open TIME NOT NULL DEFAULT '09:00',
376 dow_4_close TIME NOT NULL DEFAULT '17:00',
377 dow_5_open TIME NOT NULL DEFAULT '09:00',
378 dow_5_close TIME NOT NULL DEFAULT '17:00',
379 dow_6_open TIME NOT NULL DEFAULT '09:00',
380 dow_6_close TIME NOT NULL DEFAULT '17:00'
382 COMMENT ON TABLE actor.hours_of_operation IS $$
383 When does this org_unit usually open and close? (Variations
384 are expressed in the actor.org_unit_closed table.)
386 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
387 When does this org_unit open on Monday?
389 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
390 When does this org_unit close on Monday?
392 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
393 When does this org_unit open on Tuesday?
395 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
396 When does this org_unit close on Tuesday?
398 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
399 When does this org_unit open on Wednesday?
401 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
402 When does this org_unit close on Wednesday?
404 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
405 When does this org_unit open on Thursday?
407 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
408 When does this org_unit close on Thursday?
410 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
411 When does this org_unit open on Friday?
413 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
414 When does this org_unit close on Friday?
416 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
417 When does this org_unit open on Saturday?
419 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
420 When does this org_unit close on Saturday?
422 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
423 When does this org_unit open on Sunday?
425 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
426 When does this org_unit close on Sunday?
429 CREATE TABLE actor.org_unit_closed (
430 id SERIAL PRIMARY KEY,
431 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
432 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
433 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
437 -- Workstation registration...
438 CREATE TABLE actor.workstation (
439 id SERIAL PRIMARY KEY,
440 name TEXT NOT NULL UNIQUE,
441 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
444 CREATE TABLE actor.usr_org_unit_opt_in (
445 id SERIAL PRIMARY KEY,
446 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
447 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
448 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
449 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
450 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
451 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
453 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
455 CREATE TABLE actor.org_unit_setting (
456 id BIGSERIAL PRIMARY KEY,
457 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
458 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
460 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
462 COMMENT ON TABLE actor.org_unit_setting IS $$
465 This table contains any arbitrary settings that a client
466 program would like to save for an org unit.
469 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
472 CREATE TABLE actor.usr_address (
473 id SERIAL PRIMARY KEY,
474 valid BOOL NOT NULL DEFAULT TRUE,
475 within_city_limits BOOL NOT NULL DEFAULT TRUE,
476 address_type TEXT NOT NULL DEFAULT 'MAILING',
477 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
478 street1 TEXT NOT NULL,
483 country TEXT NOT NULL,
484 post_code TEXT NOT NULL,
485 pending BOOL NOT NULL DEFAULT FALSE,
486 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
489 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
491 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
492 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
494 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
495 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
496 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
498 CREATE TABLE actor.usr_password_reset (
499 id SERIAL PRIMARY KEY,
501 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
502 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
503 has_been_reset BOOL NOT NULL DEFAULT false
505 COMMENT ON TABLE actor.usr_password_reset IS $$
506 Self-serve password reset requests
508 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
509 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
510 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
511 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
513 CREATE TABLE actor.org_address (
514 id SERIAL PRIMARY KEY,
515 valid BOOL NOT NULL DEFAULT TRUE,
516 address_type TEXT NOT NULL DEFAULT 'MAILING',
517 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
518 street1 TEXT NOT NULL,
523 country TEXT NOT NULL,
524 post_code TEXT NOT NULL,
528 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
530 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
531 SELECT SUBSTRING( $1, 1, 5);
534 CREATE TABLE actor.usr_standing_penalty (
535 id SERIAL PRIMARY KEY,
536 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
537 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
538 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
539 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
540 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
541 stop_date TIMESTAMP WITH TIME ZONE,
544 COMMENT ON TABLE actor.usr_standing_penalty IS $$
545 User standing penalties
548 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
549 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
552 CREATE TABLE actor.usr_saved_search (
553 id SERIAL PRIMARY KEY,
554 owner INT NOT NULL REFERENCES actor.usr (id)
556 DEFERRABLE INITIALLY DEFERRED,
558 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
559 query_text TEXT NOT NULL,
560 query_type TEXT NOT NULL
561 CONSTRAINT valid_query_text CHECK (
562 query_type IN ( 'URL' )) DEFAULT 'URL',
563 -- we may add other types someday
565 CONSTRAINT valid_target CHECK (
566 target IN ( 'record', 'metarecord', 'callnumber' )),
567 CONSTRAINT name_once_per_user UNIQUE (owner, name)