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);
157 CREATE TABLE actor.stat_cat_sip_fields (
158 field CHAR(2) PRIMARY KEY,
160 one_only BOOL NOT NULL DEFAULT FALSE
162 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
163 Actor Statistical Category SIP Fields
165 Contains the list of valid SIP Field identifiers for
166 Statistical Categories.
169 CREATE TABLE actor.stat_cat (
170 id SERIAL PRIMARY KEY,
173 opac_visible BOOL NOT NULL DEFAULT FALSE,
174 usr_summary BOOL NOT NULL DEFAULT FALSE,
175 sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
177 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
179 COMMENT ON TABLE actor.stat_cat IS $$
180 User Statistical Catagories
182 Local data collected about Users is placed into a Statistical
183 Catagory. Here's where those catagories are defined.
187 CREATE TABLE actor.stat_cat_entry (
188 id SERIAL PRIMARY KEY,
189 stat_cat INT NOT NULL,
192 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
194 COMMENT ON TABLE actor.stat_cat_entry IS $$
195 User Statistical Catagory Entries
197 Local data collected about Users is placed into a Statistical
198 Catagory. Each library can create entries into any of its own
199 stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
203 CREATE TABLE actor.stat_cat_entry_usr_map (
204 id BIGSERIAL PRIMARY KEY,
205 stat_cat_entry TEXT NOT NULL,
206 stat_cat INT NOT NULL,
207 target_usr INT NOT NULL,
208 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
210 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
211 Statistical Catagory Entry to User map
213 Records the stat_cat entries for each user.
216 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
218 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
220 sipfield actor.stat_cat_sip_fields%ROWTYPE;
223 IF NEW.sip_field IS NOT NULL THEN
224 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
225 IF sipfield.one_only THEN
226 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
227 IF use_count > 0 THEN
228 RAISE EXCEPTION 'Sip field cannot be used twice';
234 $func$ LANGUAGE PLPGSQL;
236 CREATE TRIGGER actor_stat_cat_sip_update_trigger
237 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
238 EXECUTE PROCEDURE actor.stat_cat_check();
240 CREATE TABLE actor.card (
241 id SERIAL PRIMARY KEY,
242 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
243 barcode TEXT NOT NULL UNIQUE,
244 active BOOL NOT NULL DEFAULT TRUE
246 COMMENT ON TABLE actor.card IS $$
249 Each User has one or more library cards. The current "main"
250 card is linked to here from the actor.usr table, and it is up
251 to the consortium policy whether more than one card can be
252 active for any one user at a given time.
255 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
256 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
258 CREATE TABLE actor.org_unit_type (
259 id SERIAL PRIMARY KEY,
261 opac_label TEXT NOT NULL,
263 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
264 can_have_vols BOOL NOT NULL DEFAULT TRUE,
265 can_have_users BOOL NOT NULL DEFAULT TRUE
267 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
269 CREATE TABLE actor.org_unit (
270 id SERIAL PRIMARY KEY,
271 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
272 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
277 shortname TEXT NOT NULL UNIQUE,
278 name TEXT NOT NULL UNIQUE,
281 opac_visible BOOL NOT NULL DEFAULT TRUE,
282 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
284 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
285 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
286 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
287 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
288 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
289 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
291 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
293 current_aou actor.org_unit%ROWTYPE;
299 seen_ous := ARRAY[NEW.id];
300 IF TG_OP = 'INSERT' OR NEW.parent_ou IS DISTINCT FROM OLD.parent_ou THEN
302 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
303 RETURN NEW; -- No loop. Carry on.
305 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
306 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
309 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
310 seen_ous := seen_ous || current_aou.id;
311 depth_count := depth_count + 1;
312 IF depth_count = 100 THEN
313 RAISE 'OU CHECK TOO DEEP';
321 CREATE TRIGGER actor_org_unit_parent_protect_trigger
322 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
323 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
325 CREATE TABLE actor.org_lasso (
326 id SERIAL PRIMARY KEY,
330 CREATE TABLE actor.org_lasso_map (
331 id SERIAL PRIMARY KEY,
332 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
333 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
335 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
336 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
338 CREATE TABLE actor.org_unit_proximity (
339 id BIGSERIAL PRIMARY KEY,
344 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
346 CREATE TABLE actor.hours_of_operation (
347 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
348 dow_0_open TIME NOT NULL DEFAULT '09:00',
349 dow_0_close TIME NOT NULL DEFAULT '17:00',
350 dow_1_open TIME NOT NULL DEFAULT '09:00',
351 dow_1_close TIME NOT NULL DEFAULT '17:00',
352 dow_2_open TIME NOT NULL DEFAULT '09:00',
353 dow_2_close TIME NOT NULL DEFAULT '17:00',
354 dow_3_open TIME NOT NULL DEFAULT '09:00',
355 dow_3_close TIME NOT NULL DEFAULT '17:00',
356 dow_4_open TIME NOT NULL DEFAULT '09:00',
357 dow_4_close TIME NOT NULL DEFAULT '17:00',
358 dow_5_open TIME NOT NULL DEFAULT '09:00',
359 dow_5_close TIME NOT NULL DEFAULT '17:00',
360 dow_6_open TIME NOT NULL DEFAULT '09:00',
361 dow_6_close TIME NOT NULL DEFAULT '17:00'
363 COMMENT ON TABLE actor.hours_of_operation IS $$
364 When does this org_unit usually open and close? (Variations
365 are expressed in the actor.org_unit_closed table.)
367 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
368 When does this org_unit open on Monday?
370 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
371 When does this org_unit close on Monday?
373 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
374 When does this org_unit open on Tuesday?
376 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
377 When does this org_unit close on Tuesday?
379 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
380 When does this org_unit open on Wednesday?
382 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
383 When does this org_unit close on Wednesday?
385 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
386 When does this org_unit open on Thursday?
388 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
389 When does this org_unit close on Thursday?
391 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
392 When does this org_unit open on Friday?
394 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
395 When does this org_unit close on Friday?
397 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
398 When does this org_unit open on Saturday?
400 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
401 When does this org_unit close on Saturday?
403 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
404 When does this org_unit open on Sunday?
406 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
407 When does this org_unit close on Sunday?
410 CREATE TABLE actor.org_unit_closed (
411 id SERIAL PRIMARY KEY,
412 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
413 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
414 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
418 -- Workstation registration...
419 CREATE TABLE actor.workstation (
420 id SERIAL PRIMARY KEY,
421 name TEXT NOT NULL UNIQUE,
422 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
425 CREATE TABLE actor.usr_org_unit_opt_in (
426 id SERIAL PRIMARY KEY,
427 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
428 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
429 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
430 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
431 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
432 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
434 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
436 CREATE TABLE actor.org_unit_setting (
437 id BIGSERIAL PRIMARY KEY,
438 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
439 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
441 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
443 COMMENT ON TABLE actor.org_unit_setting IS $$
446 This table contains any arbitrary settings that a client
447 program would like to save for an org unit.
450 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
453 CREATE TABLE actor.usr_address (
454 id SERIAL PRIMARY KEY,
455 valid BOOL NOT NULL DEFAULT TRUE,
456 within_city_limits BOOL NOT NULL DEFAULT TRUE,
457 address_type TEXT NOT NULL DEFAULT 'MAILING',
458 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
459 street1 TEXT NOT NULL,
464 country TEXT NOT NULL,
465 post_code TEXT NOT NULL,
466 pending BOOL NOT NULL DEFAULT FALSE,
467 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
470 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
472 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
473 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
475 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
476 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
477 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
479 CREATE TABLE actor.usr_password_reset (
480 id SERIAL PRIMARY KEY,
482 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
483 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
484 has_been_reset BOOL NOT NULL DEFAULT false
486 COMMENT ON TABLE actor.usr_password_reset IS $$
487 Self-serve password reset requests
489 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
490 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
491 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
492 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
494 CREATE TABLE actor.org_address (
495 id SERIAL PRIMARY KEY,
496 valid BOOL NOT NULL DEFAULT TRUE,
497 address_type TEXT NOT NULL DEFAULT 'MAILING',
498 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
499 street1 TEXT NOT NULL,
504 country TEXT NOT NULL,
505 post_code TEXT NOT NULL,
509 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
511 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
512 SELECT SUBSTRING( $1, 1, 5);
515 CREATE TABLE actor.usr_standing_penalty (
516 id SERIAL PRIMARY KEY,
517 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
518 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
519 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
520 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
521 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
522 stop_date TIMESTAMP WITH TIME ZONE,
525 COMMENT ON TABLE actor.usr_standing_penalty IS $$
526 User standing penalties
529 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
530 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
533 CREATE TABLE actor.usr_saved_search (
534 id SERIAL PRIMARY KEY,
535 owner INT NOT NULL REFERENCES actor.usr (id)
537 DEFERRABLE INITIALLY DEFERRED,
539 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
540 query_text TEXT NOT NULL,
541 query_type TEXT NOT NULL
542 CONSTRAINT valid_query_text CHECK (
543 query_type IN ( 'URL' )) DEFAULT 'URL',
544 -- we may add other types someday
546 CONSTRAINT valid_target CHECK (
547 target IN ( 'record', 'metarecord', 'callnumber' )),
548 CONSTRAINT name_once_per_user UNIQUE (owner, name)