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_mailing_address_idx ON actor.usr (mailing_address);
97 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
99 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name));
100 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name));
101 CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name));
103 CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email));
105 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone));
106 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone));
107 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone));
109 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value));
110 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2));
112 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
114 NEW.passwd = MD5( NEW.passwd );
119 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
121 IF NEW.passwd <> OLD.passwd THEN
122 NEW.passwd = MD5( NEW.passwd );
128 CREATE TRIGGER actor_crypt_pw_update_trigger
129 BEFORE UPDATE ON actor.usr FOR EACH ROW
130 EXECUTE PROCEDURE actor.crypt_pw_update ();
132 CREATE TRIGGER actor_crypt_pw_insert_trigger
133 BEFORE INSERT ON actor.usr FOR EACH ROW
134 EXECUTE PROCEDURE actor.crypt_pw_insert ();
136 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;
138 CREATE TABLE actor.usr_note (
139 id BIGSERIAL PRIMARY KEY,
140 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
141 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
142 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
143 pub BOOL NOT NULL DEFAULT FALSE,
147 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
148 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
150 CREATE TABLE actor.usr_setting (
151 id BIGSERIAL PRIMARY KEY,
152 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
153 name TEXT NOT NULL REFERENCES config.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
155 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
157 COMMENT ON TABLE actor.usr_setting IS $$
159 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
160 * Mike Rylander <mrylander@gmail.com>
164 * This table contains any arbitrary settings that a client
165 * program would like to save for a user.
169 * This program is free software; you can redistribute it and/or
170 * modify it under the terms of the GNU General Public License
171 * as published by the Free Software Foundation; either version 2
172 * of the License, or (at your option) any later version.
174 * This program is distributed in the hope that it will be useful,
175 * but WITHOUT ANY WARRANTY; without even the implied warranty of
176 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
177 * GNU General Public License for more details.
181 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
184 CREATE TABLE actor.stat_cat (
185 id SERIAL PRIMARY KEY,
188 opac_visible BOOL NOT NULL DEFAULT FALSE,
189 usr_summary BOOL NOT NULL DEFAULT FALSE,
190 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
192 COMMENT ON TABLE actor.stat_cat IS $$
194 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
195 * Mike Rylander <mrylander@gmail.com>
197 * User Statistical Catagories
199 * Local data collected about Users is placed into a Statistical
200 * Catagory. Here's where those catagories are defined.
204 * This program is free software; you can redistribute it and/or
205 * modify it under the terms of the GNU General Public License
206 * as published by the Free Software Foundation; either version 2
207 * of the License, or (at your option) any later version.
209 * This program is distributed in the hope that it will be useful,
210 * but WITHOUT ANY WARRANTY; without even the implied warranty of
211 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
212 * GNU General Public License for more details.
217 CREATE TABLE actor.stat_cat_entry (
218 id SERIAL PRIMARY KEY,
219 stat_cat INT NOT NULL,
222 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
224 COMMENT ON TABLE actor.stat_cat_entry IS $$
226 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
227 * Mike Rylander <mrylander@gmail.com>
229 * User Statistical Catagory Entries
231 * Local data collected about Users is placed into a Statistical
232 * Catagory. Each library can create entries into any of its own
233 * stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
238 * This program is free software; you can redistribute it and/or
239 * modify it under the terms of the GNU General Public License
240 * as published by the Free Software Foundation; either version 2
241 * of the License, or (at your option) any later version.
243 * This program is distributed in the hope that it will be useful,
244 * but WITHOUT ANY WARRANTY; without even the implied warranty of
245 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
246 * GNU General Public License for more details.
251 CREATE TABLE actor.stat_cat_entry_usr_map (
252 id BIGSERIAL PRIMARY KEY,
253 stat_cat_entry TEXT NOT NULL,
254 stat_cat INT NOT NULL,
255 target_usr INT NOT NULL,
256 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
258 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
260 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
261 * Mike Rylander <mrylander@gmail.com>
263 * Statistical Catagory Entry to User map
265 * Records the stat_cat entries for each user.
270 * This program is free software; you can redistribute it and/or
271 * modify it under the terms of the GNU General Public License
272 * as published by the Free Software Foundation; either version 2
273 * of the License, or (at your option) any later version.
275 * This program is distributed in the hope that it will be useful,
276 * but WITHOUT ANY WARRANTY; without even the implied warranty of
277 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
278 * GNU General Public License for more details.
282 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
284 CREATE TABLE actor.card (
285 id SERIAL PRIMARY KEY,
286 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
287 barcode TEXT NOT NULL UNIQUE,
288 active BOOL NOT NULL DEFAULT TRUE
290 COMMENT ON TABLE actor.card IS $$
292 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
293 * Mike Rylander <mrylander@gmail.com>
297 * Each User has one or more library cards. The current "main"
298 * card is linked to here from the actor.usr table, and it is up
299 * to the consortium policy whether more than one card can be
300 * active for any one user at a given time.
305 * This program is free software; you can redistribute it and/or
306 * modify it under the terms of the GNU General Public License
307 * as published by the Free Software Foundation; either version 2
308 * of the License, or (at your option) any later version.
310 * This program is distributed in the hope that it will be useful,
311 * but WITHOUT ANY WARRANTY; without even the implied warranty of
312 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
313 * GNU General Public License for more details.
317 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
318 CREATE INDEX actor_card_barcode_lower_idx ON actor.card (lower(barcode));
320 CREATE TABLE actor.org_unit_type (
321 id SERIAL PRIMARY KEY,
323 opac_label TEXT NOT NULL,
325 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
326 can_have_vols BOOL NOT NULL DEFAULT TRUE,
327 can_have_users BOOL NOT NULL DEFAULT TRUE
329 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
331 CREATE TABLE actor.org_unit (
332 id SERIAL PRIMARY KEY,
333 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
334 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
339 shortname TEXT NOT NULL UNIQUE,
340 name TEXT NOT NULL UNIQUE,
343 opac_visible BOOL NOT NULL DEFAULT TRUE,
344 fiscal_calendar INT NOT NULL DEFAULT 1 -- foreign key constraint to be added later
346 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
347 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
348 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
349 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
350 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
351 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
353 CREATE TABLE actor.org_lasso (
354 id SERIAL PRIMARY KEY,
358 CREATE TABLE actor.org_lasso_map (
359 id SERIAL PRIMARY KEY,
360 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
361 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
363 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
364 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
366 CREATE TABLE actor.org_unit_proximity (
367 id BIGSERIAL PRIMARY KEY,
372 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
374 CREATE TABLE actor.hours_of_operation (
375 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
376 dow_0_open TIME NOT NULL DEFAULT '09:00',
377 dow_0_close TIME NOT NULL DEFAULT '17:00',
378 dow_1_open TIME NOT NULL DEFAULT '09:00',
379 dow_1_close TIME NOT NULL DEFAULT '17:00',
380 dow_2_open TIME NOT NULL DEFAULT '09:00',
381 dow_2_close TIME NOT NULL DEFAULT '17:00',
382 dow_3_open TIME NOT NULL DEFAULT '09:00',
383 dow_3_close TIME NOT NULL DEFAULT '17:00',
384 dow_4_open TIME NOT NULL DEFAULT '09:00',
385 dow_4_close TIME NOT NULL DEFAULT '17:00',
386 dow_5_open TIME NOT NULL DEFAULT '09:00',
387 dow_5_close TIME NOT NULL DEFAULT '17:00',
388 dow_6_open TIME NOT NULL DEFAULT '09:00',
389 dow_6_close TIME NOT NULL DEFAULT '17:00'
391 COMMENT ON TABLE actor.hours_of_operation IS $$
392 When does this org_unit usually open and close? (Variations
393 are expressed in the actor.org_unit_closed table.)
395 COMMENT ON COLUMN actor.hours_of_operation.dow_0_open IS $$
396 When does this org_unit open on Monday?
398 COMMENT ON COLUMN actor.hours_of_operation.dow_0_close IS $$
399 When does this org_unit close on Monday?
401 COMMENT ON COLUMN actor.hours_of_operation.dow_1_open IS $$
402 When does this org_unit open on Tuesday?
404 COMMENT ON COLUMN actor.hours_of_operation.dow_1_close IS $$
405 When does this org_unit close on Tuesday?
407 COMMENT ON COLUMN actor.hours_of_operation.dow_2_open IS $$
408 When does this org_unit open on Wednesday?
410 COMMENT ON COLUMN actor.hours_of_operation.dow_2_close IS $$
411 When does this org_unit close on Wednesday?
413 COMMENT ON COLUMN actor.hours_of_operation.dow_3_open IS $$
414 When does this org_unit open on Thursday?
416 COMMENT ON COLUMN actor.hours_of_operation.dow_3_close IS $$
417 When does this org_unit close on Thursday?
419 COMMENT ON COLUMN actor.hours_of_operation.dow_4_open IS $$
420 When does this org_unit open on Friday?
422 COMMENT ON COLUMN actor.hours_of_operation.dow_4_close IS $$
423 When does this org_unit close on Friday?
425 COMMENT ON COLUMN actor.hours_of_operation.dow_5_open IS $$
426 When does this org_unit open on Saturday?
428 COMMENT ON COLUMN actor.hours_of_operation.dow_5_close IS $$
429 When does this org_unit close on Saturday?
431 COMMENT ON COLUMN actor.hours_of_operation.dow_6_open IS $$
432 When does this org_unit open on Sunday?
434 COMMENT ON COLUMN actor.hours_of_operation.dow_6_close IS $$
435 When does this org_unit close on Sunday?
438 CREATE TABLE actor.org_unit_closed (
439 id SERIAL PRIMARY KEY,
440 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
441 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
442 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
446 -- Workstation registration...
447 CREATE TABLE actor.workstation (
448 id SERIAL PRIMARY KEY,
449 name TEXT NOT NULL UNIQUE,
450 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
453 CREATE TABLE actor.usr_org_unit_opt_in (
454 id SERIAL PRIMARY KEY,
455 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
456 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
457 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
458 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
459 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
460 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
462 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
464 CREATE TABLE actor.org_unit_setting (
465 id BIGSERIAL PRIMARY KEY,
466 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
467 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
469 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
471 COMMENT ON TABLE actor.org_unit_setting IS $$
473 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
474 * Mike Rylander <mrylander@gmail.com>
478 * This table contains any arbitrary settings that a client
479 * program would like to save for an org unit.
483 * This program is free software; you can redistribute it and/or
484 * modify it under the terms of the GNU General Public License
485 * as published by the Free Software Foundation; either version 2
486 * of the License, or (at your option) any later version.
488 * This program is distributed in the hope that it will be useful,
489 * but WITHOUT ANY WARRANTY; without even the implied warranty of
490 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
491 * GNU General Public License for more details.
495 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
498 CREATE TABLE actor.usr_address (
499 id SERIAL PRIMARY KEY,
500 valid BOOL NOT NULL DEFAULT TRUE,
501 within_city_limits BOOL NOT NULL DEFAULT TRUE,
502 address_type TEXT NOT NULL DEFAULT 'MAILING',
503 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
504 street1 TEXT NOT NULL,
509 country TEXT NOT NULL,
510 post_code TEXT NOT NULL,
511 pending BOOL NOT NULL DEFAULT FALSE,
512 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
515 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
517 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
518 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
520 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
521 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
522 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
524 CREATE TABLE actor.usr_password_reset (
525 id SERIAL PRIMARY KEY,
527 usr BIGINT NOT NULL REFERENCES actor.usr(id) DEFERRABLE INITIALLY DEFERRED,
528 request_time TIMESTAMP NOT NULL DEFAULT NOW(),
529 has_been_reset BOOL NOT NULL DEFAULT false
531 COMMENT ON TABLE actor.usr_password_reset IS $$
533 * Copyright (C) 2010 Laurentian University
534 * Dan Scott <dscott@laurentian.ca>
536 * Self-serve password reset requests
540 * This program is free software; you can redistribute it and/or
541 * modify it under the terms of the GNU General Public License
542 * as published by the Free Software Foundation; either version 2
543 * of the License, or (at your option) any later version.
545 * This program is distributed in the hope that it will be useful,
546 * but WITHOUT ANY WARRANTY; without even the implied warranty of
547 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
548 * GNU General Public License for more details.
551 CREATE UNIQUE INDEX actor_usr_password_reset_uuid_idx ON actor.usr_password_reset (uuid);
552 CREATE INDEX actor_usr_password_reset_usr_idx ON actor.usr_password_reset (usr);
553 CREATE INDEX actor_usr_password_reset_request_time_idx ON actor.usr_password_reset (request_time);
554 CREATE INDEX actor_usr_password_reset_has_been_reset_idx ON actor.usr_password_reset (has_been_reset);
556 CREATE TABLE actor.org_address (
557 id SERIAL PRIMARY KEY,
558 valid BOOL NOT NULL DEFAULT TRUE,
559 address_type TEXT NOT NULL DEFAULT 'MAILING',
560 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
561 street1 TEXT NOT NULL,
566 country TEXT NOT NULL,
567 post_code TEXT NOT NULL,
571 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
573 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
574 SELECT SUBSTRING( $1, 1, 5);
577 CREATE TABLE actor.usr_standing_penalty (
578 id SERIAL PRIMARY KEY,
579 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
580 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
581 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
582 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
583 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
584 stop_date TIMESTAMP WITH TIME ZONE,
587 COMMENT ON TABLE actor.usr_standing_penalty IS $$
589 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
590 * Mike Rylander <mrylander@gmail.com>
592 * User standing penalties
596 * This program is free software; you can redistribute it and/or
597 * modify it under the terms of the GNU General Public License
598 * as published by the Free Software Foundation; either version 2
599 * of the License, or (at your option) any later version.
601 * This program is distributed in the hope that it will be useful,
602 * but WITHOUT ANY WARRANTY; without even the implied warranty of
603 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
604 * GNU General Public License for more details.
608 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
609 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );
612 CREATE TABLE actor.usr_saved_search (
613 id SERIAL PRIMARY KEY,
614 owner INT NOT NULL REFERENCES actor.usr (id)
616 DEFERRABLE INITIALLY DEFERRED,
618 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
619 query_text TEXT NOT NULL,
620 query_type TEXT NOT NULL
621 CONSTRAINT valid_query_text CHECK (
622 query_type IN ( 'URL' )) DEFAULT 'URL',
623 -- we may add other types someday
625 CONSTRAINT valid_target CHECK (
626 target IN ( 'record', 'metarecord', 'callnumber' )),
627 CONSTRAINT name_once_per_user UNIQUE (owner, name)