1 DROP SCHEMA 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 usrgroup SERIAL NOT NULL,
61 claims_returned_count INT NOT NULL DEFAULT 0,
62 credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00,
63 last_xact_id TEXT NOT NULL DEFAULT 'none',
65 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
66 expire_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + '3 years'::INTERVAL)
68 COMMENT ON TABLE actor.usr IS $$
70 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
71 * Mike Rylander <mrylander@gmail.com>
75 * This table contains the core User objects that describe both
76 * staff members and patrons. The difference between the two
77 * types of users is based on the user's permissions.
81 * This program is free software; you can redistribute it and/or
82 * modify it under the terms of the GNU General Public License
83 * as published by the Free Software Foundation; either version 2
84 * of the License, or (at your option) any later version.
86 * This program is distributed in the hope that it will be useful,
87 * but WITHOUT ANY WARRANTY; without even the implied warranty of
88 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
89 * GNU General Public License for more details.
93 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
94 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
95 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
97 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name));
98 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name));
99 CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name));
101 CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email));
103 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone));
104 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone));
105 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone));
107 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value));
108 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2));
110 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
112 NEW.passwd = MD5( NEW.passwd );
117 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
119 IF NEW.passwd <> OLD.passwd THEN
120 NEW.passwd = MD5( NEW.passwd );
126 CREATE TRIGGER actor_crypt_pw_update_trigger
127 BEFORE UPDATE ON actor.usr FOR EACH ROW
128 EXECUTE PROCEDURE actor.crypt_pw_update ();
130 CREATE TRIGGER actor_crypt_pw_insert_trigger
131 BEFORE INSERT ON actor.usr FOR EACH ROW
132 EXECUTE PROCEDURE actor.crypt_pw_insert ();
134 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;
136 CREATE TABLE actor.usr_note (
137 id BIGSERIAL PRIMARY KEY,
138 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
139 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
140 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
141 pub BOOL NOT NULL DEFAULT FALSE,
145 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
147 CREATE TABLE actor.usr_setting (
148 id BIGSERIAL PRIMARY KEY,
149 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
152 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
154 COMMENT ON TABLE actor.usr_setting IS $$
156 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
157 * Mike Rylander <mrylander@gmail.com>
161 * This table contains any arbitrary settings that a client
162 * program would like to save for a user.
166 * This program is free software; you can redistribute it and/or
167 * modify it under the terms of the GNU General Public License
168 * as published by the Free Software Foundation; either version 2
169 * of the License, or (at your option) any later version.
171 * This program is distributed in the hope that it will be useful,
172 * but WITHOUT ANY WARRANTY; without even the implied warranty of
173 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
174 * GNU General Public License for more details.
178 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
181 CREATE TABLE actor.stat_cat (
182 id SERIAL PRIMARY KEY,
185 opac_visible BOOL NOT NULL DEFAULT FALSE,
186 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
188 COMMENT ON TABLE actor.stat_cat IS $$
190 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
191 * Mike Rylander <mrylander@gmail.com>
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 * This program is free software; you can redistribute it and/or
201 * modify it under the terms of the GNU General Public License
202 * as published by the Free Software Foundation; either version 2
203 * of the License, or (at your option) any later version.
205 * This program is distributed in the hope that it will be useful,
206 * but WITHOUT ANY WARRANTY; without even the implied warranty of
207 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
208 * GNU General Public License for more details.
213 CREATE TABLE actor.stat_cat_entry (
214 id SERIAL PRIMARY KEY,
215 stat_cat INT NOT NULL,
218 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
220 COMMENT ON TABLE actor.stat_cat_entry IS $$
222 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
223 * Mike Rylander <mrylander@gmail.com>
225 * User Statistical Catagory Entries
227 * Local data collected about Users is placed into a Statistical
228 * Catagory. Each library can create entries into any of its own
229 * stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
234 * This program is free software; you can redistribute it and/or
235 * modify it under the terms of the GNU General Public License
236 * as published by the Free Software Foundation; either version 2
237 * of the License, or (at your option) any later version.
239 * This program is distributed in the hope that it will be useful,
240 * but WITHOUT ANY WARRANTY; without even the implied warranty of
241 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
242 * GNU General Public License for more details.
247 CREATE TABLE actor.stat_cat_entry_usr_map (
248 id BIGSERIAL PRIMARY KEY,
249 stat_cat_entry TEXT NOT NULL,
250 stat_cat INT NOT NULL,
251 target_usr INT NOT NULL,
252 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
254 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
256 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
257 * Mike Rylander <mrylander@gmail.com>
259 * Statistical Catagory Entry to User map
261 * Records the stat_cat entries for each user.
266 * This program is free software; you can redistribute it and/or
267 * modify it under the terms of the GNU General Public License
268 * as published by the Free Software Foundation; either version 2
269 * of the License, or (at your option) any later version.
271 * This program is distributed in the hope that it will be useful,
272 * but WITHOUT ANY WARRANTY; without even the implied warranty of
273 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
274 * GNU General Public License for more details.
278 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
280 CREATE TABLE actor.card (
281 id SERIAL PRIMARY KEY,
282 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
283 barcode TEXT NOT NULL UNIQUE,
284 active BOOL NOT NULL DEFAULT TRUE
286 COMMENT ON TABLE actor.card IS $$
288 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
289 * Mike Rylander <mrylander@gmail.com>
293 * Each User has one or more library cards. The current "main"
294 * card is linked to here from the actor.usr table, and it is up
295 * to the consortium policy whether more than one card can be
296 * active for any one user at a given time.
301 * This program is free software; you can redistribute it and/or
302 * modify it under the terms of the GNU General Public License
303 * as published by the Free Software Foundation; either version 2
304 * of the License, or (at your option) any later version.
306 * This program is distributed in the hope that it will be useful,
307 * but WITHOUT ANY WARRANTY; without even the implied warranty of
308 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
309 * GNU General Public License for more details.
313 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
315 CREATE TABLE actor.org_unit_type (
316 id SERIAL PRIMARY KEY,
318 opac_label TEXT NOT NULL,
320 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
321 can_have_vols BOOL NOT NULL DEFAULT TRUE,
322 can_have_users BOOL NOT NULL DEFAULT TRUE
324 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
326 CREATE TABLE actor.org_unit (
327 id SERIAL PRIMARY KEY,
328 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
329 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
334 shortname TEXT NOT NULL,
338 opac_visible BOOL NOT NULL DEFAULT TRUE
340 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
341 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
342 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
343 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
344 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
345 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
347 CREATE TABLE actor.org_lasso (
348 id SERIAL PRIMARY KEY,
352 CREATE TABLE actor.org_lasso_map (
353 id SERIAL PRIMARY KEY,
354 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
355 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
357 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
358 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
360 CREATE TABLE actor.org_unit_proximity (
361 id BIGSERIAL PRIMARY KEY,
366 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
368 CREATE TABLE actor.hours_of_operation (
369 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
370 dow_0_open TIME NOT NULL DEFAULT '09:00',
371 dow_0_close TIME NOT NULL DEFAULT '17:00',
372 dow_1_open TIME NOT NULL DEFAULT '09:00',
373 dow_1_close TIME NOT NULL DEFAULT '17:00',
374 dow_2_open TIME NOT NULL DEFAULT '09:00',
375 dow_2_close TIME NOT NULL DEFAULT '17:00',
376 dow_3_open TIME NOT NULL DEFAULT '09:00',
377 dow_3_close TIME NOT NULL DEFAULT '17:00',
378 dow_4_open TIME NOT NULL DEFAULT '09:00',
379 dow_4_close TIME NOT NULL DEFAULT '17:00',
380 dow_5_open TIME NOT NULL DEFAULT '09:00',
381 dow_5_close TIME NOT NULL DEFAULT '17:00',
382 dow_6_open TIME NOT NULL DEFAULT '09:00',
383 dow_6_close TIME NOT NULL DEFAULT '17:00'
386 CREATE TABLE actor.org_unit_closed (
387 id SERIAL PRIMARY KEY,
388 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
389 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
390 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
394 -- Workstation registration...
395 CREATE TABLE actor.workstation (
396 id SERIAL PRIMARY KEY,
397 name TEXT NOT NULL UNIQUE,
398 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
401 CREATE TABLE actor.usr_org_unit_opt_in (
402 id SERIAL PRIMARY KEY,
403 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
404 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
405 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
406 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
407 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
408 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
411 CREATE TABLE actor.org_unit_setting (
412 id BIGSERIAL PRIMARY KEY,
413 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
416 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
418 COMMENT ON TABLE actor.org_unit_setting IS $$
420 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
421 * Mike Rylander <mrylander@gmail.com>
425 * This table contains any arbitrary settings that a client
426 * program would like to save for an org unit.
430 * This program is free software; you can redistribute it and/or
431 * modify it under the terms of the GNU General Public License
432 * as published by the Free Software Foundation; either version 2
433 * of the License, or (at your option) any later version.
435 * This program is distributed in the hope that it will be useful,
436 * but WITHOUT ANY WARRANTY; without even the implied warranty of
437 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
438 * GNU General Public License for more details.
442 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
445 CREATE TABLE actor.usr_address (
446 id SERIAL PRIMARY KEY,
447 valid BOOL NOT NULL DEFAULT TRUE,
448 within_city_limits BOOL NOT NULL DEFAULT TRUE,
449 address_type TEXT NOT NULL DEFAULT 'MAILING',
450 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
451 street1 TEXT NOT NULL,
456 country TEXT NOT NULL,
457 post_code TEXT NOT NULL
460 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
462 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
463 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
465 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
466 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
467 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
470 CREATE TABLE actor.org_address (
471 id SERIAL PRIMARY KEY,
472 valid BOOL NOT NULL DEFAULT TRUE,
473 address_type TEXT NOT NULL DEFAULT 'MAILING',
474 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
475 street1 TEXT NOT NULL,
480 country TEXT NOT NULL,
481 post_code TEXT NOT NULL
484 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
486 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
487 SELECT SUBSTRING( $1, 1, 5);
490 CREATE TABLE actor.usr_standing_penalty (
491 id SERIAL PRIMARY KEY,
492 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
493 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
494 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
495 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
496 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
499 COMMENT ON TABLE actor.usr_standing_penalty IS $$
501 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
502 * Mike Rylander <mrylander@gmail.com>
504 * User standing penalties
508 * This program is free software; you can redistribute it and/or
509 * modify it under the terms of the GNU General Public License
510 * as published by the Free Software Foundation; either version 2
511 * of the License, or (at your option) any later version.
513 * This program is distributed in the hope that it will be useful,
514 * but WITHOUT ANY WARRANTY; without even the implied warranty of
515 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
516 * GNU General Public License for more details.
520 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);