1 DROP SCHEMA IF EXISTS actor CASCADE;
5 COMMENT ON SCHEMA actor IS $$
7 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
8 * Mike Rylander <mrylander@gmail.com>
12 * Holds all tables pertaining to users and libraries (org units).
16 * This program is free software; you can redistribute it and/or
17 * modify it under the terms of the GNU General Public License
18 * as published by the Free Software Foundation; either version 2
19 * of the License, or (at your option) any later version.
21 * This program is distributed in the hope that it will be useful,
22 * but WITHOUT ANY WARRANTY; without even the implied warranty of
23 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24 * GNU General Public License for more details.
28 CREATE TABLE actor.usr (
29 id SERIAL PRIMARY KEY,
30 card INT UNIQUE, -- active card
31 profile INT NOT NULL, -- patron profile
32 usrname TEXT NOT NULL UNIQUE,
35 standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id) DEFERRABLE INITIALLY DEFERRED,
36 ident_type INT NOT NULL REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
38 ident_type2 INT REFERENCES config.identification_type (id) DEFERRABLE INITIALLY DEFERRED,
40 net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id) DEFERRABLE INITIALLY DEFERRED,
43 first_given_name TEXT NOT NULL,
44 second_given_name TEXT,
45 family_name TEXT NOT NULL,
54 dob TIMESTAMP WITH TIME ZONE,
55 active BOOL NOT NULL DEFAULT TRUE,
56 master_account BOOL NOT NULL DEFAULT FALSE,
57 super_user BOOL NOT NULL DEFAULT FALSE,
58 barred BOOL NOT NULL DEFAULT FALSE,
59 deleted BOOL NOT NULL DEFAULT FALSE,
60 juvenile BOOL NOT NULL DEFAULT FALSE,
61 usrgroup SERIAL NOT NULL,
62 claims_returned_count INT NOT NULL DEFAULT 0,
63 credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00,
64 last_xact_id TEXT NOT NULL DEFAULT 'none',
66 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
67 expire_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + '3 years'::INTERVAL),
68 claims_never_checked_out_count INT NOT NULL DEFAULT 0
70 COMMENT ON TABLE actor.usr IS $$
72 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
73 * Mike Rylander <mrylander@gmail.com>
77 * This table contains the core User objects that describe both
78 * staff members and patrons. The difference between the two
79 * types of users is based on the user's permissions.
83 * This program is free software; you can redistribute it and/or
84 * modify it under the terms of the GNU General Public License
85 * as published by the Free Software Foundation; either version 2
86 * of the License, or (at your option) any later version.
88 * This program is distributed in the hope that it will be useful,
89 * but WITHOUT ANY WARRANTY; without even the implied warranty of
90 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
91 * GNU General Public License for more details.
95 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
96 CREATE INDEX actor_usr_usrgroup_idx ON actor.usr (usrgroup);
97 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
98 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
100 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name));
101 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name));
102 CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name));
104 CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email));
106 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone));
107 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone));
108 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone));
110 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value));
111 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2));
113 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
115 NEW.passwd = MD5( NEW.passwd );
120 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
122 IF NEW.passwd <> OLD.passwd THEN
123 NEW.passwd = MD5( NEW.passwd );
129 CREATE TRIGGER actor_crypt_pw_update_trigger
130 BEFORE UPDATE ON actor.usr FOR EACH ROW
131 EXECUTE PROCEDURE actor.crypt_pw_update ();
133 CREATE TRIGGER actor_crypt_pw_insert_trigger
134 BEFORE INSERT ON actor.usr FOR EACH ROW
135 EXECUTE PROCEDURE actor.crypt_pw_insert ();
137 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;
139 CREATE TABLE actor.usr_note (
140 id BIGSERIAL PRIMARY KEY,
141 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
142 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
143 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
144 pub BOOL NOT NULL DEFAULT FALSE,
148 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
149 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
151 CREATE TABLE actor.usr_setting (
152 id BIGSERIAL PRIMARY KEY,
153 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
154 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
156 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
158 COMMENT ON TABLE actor.usr_setting IS $$
160 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
161 * Mike Rylander <mrylander@gmail.com>
165 * This table contains any arbitrary settings that a client
166 * program would like to save for a user.
170 * This program is free software; you can redistribute it and/or
171 * modify it under the terms of the GNU General Public License
172 * as published by the Free Software Foundation; either version 2
173 * of the License, or (at your option) any later version.
175 * This program is distributed in the hope that it will be useful,
176 * but WITHOUT ANY WARRANTY; without even the implied warranty of
177 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
178 * GNU General Public License for more details.
182 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
185 CREATE TABLE actor.stat_cat (
186 id SERIAL PRIMARY KEY,
189 opac_visible BOOL NOT NULL DEFAULT FALSE,
190 usr_summary BOOL NOT NULL DEFAULT FALSE,
191 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
193 COMMENT ON TABLE actor.stat_cat IS $$
195 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
196 * Mike Rylander <mrylander@gmail.com>
198 * User Statistical Catagories
200 * Local data collected about Users is placed into a Statistical
201 * Catagory. Here's where those catagories are defined.
205 * This program is free software; you can redistribute it and/or
206 * modify it under the terms of the GNU General Public License
207 * as published by the Free Software Foundation; either version 2
208 * of the License, or (at your option) any later version.
210 * This program is distributed in the hope that it will be useful,
211 * but WITHOUT ANY WARRANTY; without even the implied warranty of
212 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
213 * GNU General Public License for more details.
218 CREATE TABLE actor.stat_cat_entry (
219 id SERIAL PRIMARY KEY,
220 stat_cat INT NOT NULL,
223 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
225 COMMENT ON TABLE actor.stat_cat_entry IS $$
227 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
228 * Mike Rylander <mrylander@gmail.com>
230 * User Statistical Catagory Entries
232 * Local data collected about Users is placed into a Statistical
233 * Catagory. Each library can create entries into any of its own
234 * stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
239 * This program is free software; you can redistribute it and/or
240 * modify it under the terms of the GNU General Public License
241 * as published by the Free Software Foundation; either version 2
242 * of the License, or (at your option) any later version.
244 * This program is distributed in the hope that it will be useful,
245 * but WITHOUT ANY WARRANTY; without even the implied warranty of
246 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
247 * GNU General Public License for more details.
252 CREATE TABLE actor.stat_cat_entry_usr_map (
253 id BIGSERIAL PRIMARY KEY,
254 stat_cat_entry TEXT NOT NULL,
255 stat_cat INT NOT NULL,
256 target_usr INT NOT NULL,
257 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
259 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
261 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
262 * Mike Rylander <mrylander@gmail.com>
264 * Statistical Catagory Entry to User map
266 * Records the stat_cat entries for each user.
271 * This program is free software; you can redistribute it and/or
272 * modify it under the terms of the GNU General Public License
273 * as published by the Free Software Foundation; either version 2
274 * of the License, or (at your option) any later version.
276 * This program is distributed in the hope that it will be useful,
277 * but WITHOUT ANY WARRANTY; without even the implied warranty of
278 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
279 * GNU General Public License for more details.
283 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
285 CREATE TABLE actor.card (
286 id SERIAL PRIMARY KEY,
287 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
288 barcode TEXT NOT NULL UNIQUE,
289 active BOOL NOT NULL DEFAULT TRUE
291 COMMENT ON TABLE actor.card IS $$
293 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
294 * Mike Rylander <mrylander@gmail.com>
298 * Each User has one or more library cards. The current "main"
299 * card is linked to here from the actor.usr table, and it is up
300 * to the consortium policy whether more than one card can be
301 * active for any one user at a given time.
306 * This program is free software; you can redistribute it and/or
307 * modify it under the terms of the GNU General Public License
308 * as published by the Free Software Foundation; either version 2
309 * of the License, or (at your option) any later version.
311 * This program is distributed in the hope that it will be useful,
312 * but WITHOUT ANY WARRANTY; without even the implied warranty of
313 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
314 * GNU General Public License for more details.
318 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
319 CREATE INDEX actor_card_barcode_lower_idx ON actor.card (lower(barcode));
321 CREATE TABLE actor.org_unit_type (
322 id SERIAL PRIMARY KEY,
324 opac_label TEXT NOT NULL,
326 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
327 can_have_vols BOOL NOT NULL DEFAULT TRUE,
328 can_have_users BOOL NOT NULL DEFAULT TRUE
330 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
332 CREATE TABLE actor.org_unit (
333 id SERIAL PRIMARY KEY,
334 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
335 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
340 shortname TEXT NOT NULL UNIQUE,
341 name TEXT NOT NULL UNIQUE,
344 opac_visible BOOL NOT NULL DEFAULT TRUE,
345 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
347 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
348 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
349 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
350 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
351 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
352 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
354 CREATE TABLE actor.org_lasso (
355 id SERIAL PRIMARY KEY,
359 CREATE TABLE actor.org_lasso_map (
360 id SERIAL PRIMARY KEY,
361 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
362 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
364 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
365 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
367 CREATE TABLE actor.org_unit_proximity (
368 id BIGSERIAL PRIMARY KEY,
373 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
375 CREATE TABLE actor.hours_of_operation (
376 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
377 dow_0_open TIME NOT NULL DEFAULT '09:00',
378 dow_0_close TIME NOT NULL DEFAULT '17:00',
379 dow_1_open TIME NOT NULL DEFAULT '09:00',
380 dow_1_close TIME NOT NULL DEFAULT '17:00',
381 dow_2_open TIME NOT NULL DEFAULT '09:00',
382 dow_2_close TIME NOT NULL DEFAULT '17:00',
383 dow_3_open TIME NOT NULL DEFAULT '09:00',
384 dow_3_close TIME NOT NULL DEFAULT '17:00',
385 dow_4_open TIME NOT NULL DEFAULT '09:00',
386 dow_4_close TIME NOT NULL DEFAULT '17:00',
387 dow_5_open TIME NOT NULL DEFAULT '09:00',
388 dow_5_close TIME NOT NULL DEFAULT '17:00',
389 dow_6_open TIME NOT NULL DEFAULT '09:00',
390 dow_6_close TIME NOT NULL DEFAULT '17:00'
392 COMMENT ON TABLE actor.hours_of_operation IS $$
393 When does this org_unit usually open and close? (Variations
394 are expressed in the actor.org_unit_closed table.)
396 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
397 When does this org_unit open on Monday?
399 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
400 When does this org_unit close on Monday?
402 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
403 When does this org_unit open on Tuesday?
405 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
406 When does this org_unit close on Tuesday?
408 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
409 When does this org_unit open on Wednesday?
411 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
412 When does this org_unit close on Wednesday?
414 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
415 When does this org_unit open on Thursday?
417 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
418 When does this org_unit close on Thursday?
420 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
421 When does this org_unit open on Friday?
423 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
424 When does this org_unit close on Friday?
426 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
427 When does this org_unit open on Saturday?
429 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
430 When does this org_unit close on Saturday?
432 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
433 When does this org_unit open on Sunday?
435 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
436 When does this org_unit close on Sunday?
439 CREATE TABLE actor.org_unit_closed (
440 id SERIAL PRIMARY KEY,
441 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
442 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
443 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
447 -- Workstation registration...
448 CREATE TABLE actor.workstation (
449 id SERIAL PRIMARY KEY,
450 name TEXT NOT NULL UNIQUE,
451 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
454 CREATE TABLE actor.usr_org_unit_opt_in (
455 id SERIAL PRIMARY KEY,
456 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
457 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
458 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
459 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
460 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
461 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
463 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
465 CREATE TABLE actor.org_unit_setting (
466 id BIGSERIAL PRIMARY KEY,
467 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
468 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
470 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
472 COMMENT ON TABLE actor.org_unit_setting IS $$
474 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
475 * Mike Rylander <mrylander@gmail.com>
479 * This table contains any arbitrary settings that a client
480 * program would like to save for an org unit.
484 * This program is free software; you can redistribute it and/or
485 * modify it under the terms of the GNU General Public License
486 * as published by the Free Software Foundation; either version 2
487 * of the License, or (at your option) any later version.
489 * This program is distributed in the hope that it will be useful,
490 * but WITHOUT ANY WARRANTY; without even the implied warranty of
491 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
492 * GNU General Public License for more details.
496 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
499 CREATE TABLE actor.usr_address (
500 id SERIAL PRIMARY KEY,
501 valid BOOL NOT NULL DEFAULT TRUE,
502 within_city_limits BOOL NOT NULL DEFAULT TRUE,
503 address_type TEXT NOT NULL DEFAULT 'MAILING',
504 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
505 street1 TEXT NOT NULL,
510 country TEXT NOT NULL,
511 post_code TEXT NOT NULL,
512 pending BOOL NOT NULL DEFAULT FALSE,
513 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
516 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
518 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
519 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
521 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
522 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
523 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
525 CREATE TABLE actor.usr_password_reset (
526 id SERIAL PRIMARY KEY,
528 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
529 request_time TIMESTAMP NOT NULL DEFAULT NOW(),
530 has_been_reset BOOL NOT NULL DEFAULT false
532 COMMENT ON TABLE actor.usr_password_reset IS $$
534 * Copyright (C) 2010 Laurentian University
535 * Dan Scott <dscott@laurentian.ca>
537 * Self-serve password reset requests
541 * This program is free software; you can redistribute it and/or
542 * modify it under the terms of the GNU General Public License
543 * as published by the Free Software Foundation; either version 2
544 * of the License, or (at your option) any later version.
546 * This program is distributed in the hope that it will be useful,
547 * but WITHOUT ANY WARRANTY; without even the implied warranty of
548 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
549 * GNU General Public License for more details.
552 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
553 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
554 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
555 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
557 CREATE TABLE actor.org_address (
558 id SERIAL PRIMARY KEY,
559 valid BOOL NOT NULL DEFAULT TRUE,
560 address_type TEXT NOT NULL DEFAULT 'MAILING',
561 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
562 street1 TEXT NOT NULL,
567 country TEXT NOT NULL,
568 post_code TEXT NOT NULL,
572 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
574 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
575 SELECT SUBSTRING( $1, 1, 5);
578 CREATE TABLE actor.usr_standing_penalty (
579 id SERIAL PRIMARY KEY,
580 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
581 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
582 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
583 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
584 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
585 stop_date TIMESTAMP WITH TIME ZONE,
588 COMMENT ON TABLE actor.usr_standing_penalty IS $$
590 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
591 * Mike Rylander <mrylander@gmail.com>
593 * User standing penalties
597 * This program is free software; you can redistribute it and/or
598 * modify it under the terms of the GNU General Public License
599 * as published by the Free Software Foundation; either version 2
600 * of the License, or (at your option) any later version.
602 * This program is distributed in the hope that it will be useful,
603 * but WITHOUT ANY WARRANTY; without even the implied warranty of
604 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
605 * GNU General Public License for more details.
609 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
610 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
613 CREATE TABLE actor.usr_saved_search (
614 id SERIAL PRIMARY KEY,
615 owner INT NOT NULL REFERENCES actor.usr (id)
617 DEFERRABLE INITIALLY DEFERRED,
619 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
620 query_text TEXT NOT NULL,
621 query_type TEXT NOT NULL
622 CONSTRAINT valid_query_text CHECK (
623 query_type IN ( 'URL' )) DEFAULT 'URL',
624 -- we may add other types someday
626 CONSTRAINT valid_target CHECK (
627 target IN ( 'record', 'metarecord', 'callnumber' )),
628 CONSTRAINT name_once_per_user UNIQUE (owner, name)