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 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)
69 COMMENT ON TABLE actor.usr IS $$
71 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
72 * Mike Rylander <mrylander@gmail.com>
76 * This table contains the core User objects that describe both
77 * staff members and patrons. The difference between the two
78 * types of users is based on the user's permissions.
82 * This program is free software; you can redistribute it and/or
83 * modify it under the terms of the GNU General Public License
84 * as published by the Free Software Foundation; either version 2
85 * of the License, or (at your option) any later version.
87 * This program is distributed in the hope that it will be useful,
88 * but WITHOUT ANY WARRANTY; without even the implied warranty of
89 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
90 * GNU General Public License for more details.
94 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
95 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
96 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
98 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name));
99 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name));
100 CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name));
102 CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email));
104 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone));
105 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone));
106 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone));
108 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value));
109 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2));
111 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
113 NEW.passwd = MD5( NEW.passwd );
118 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
120 IF NEW.passwd <> OLD.passwd THEN
121 NEW.passwd = MD5( NEW.passwd );
127 CREATE TRIGGER actor_crypt_pw_update_trigger
128 BEFORE UPDATE ON actor.usr FOR EACH ROW
129 EXECUTE PROCEDURE actor.crypt_pw_update ();
131 CREATE TRIGGER actor_crypt_pw_insert_trigger
132 BEFORE INSERT ON actor.usr FOR EACH ROW
133 EXECUTE PROCEDURE actor.crypt_pw_insert ();
135 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;
137 CREATE TABLE actor.usr_note (
138 id BIGSERIAL PRIMARY KEY,
139 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
140 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
141 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
142 pub BOOL NOT NULL DEFAULT FALSE,
146 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
147 CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
149 CREATE TABLE actor.usr_setting (
150 id BIGSERIAL PRIMARY KEY,
151 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
154 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
156 COMMENT ON TABLE actor.usr_setting IS $$
158 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
159 * Mike Rylander <mrylander@gmail.com>
163 * This table contains any arbitrary settings that a client
164 * program would like to save for a user.
168 * This program is free software; you can redistribute it and/or
169 * modify it under the terms of the GNU General Public License
170 * as published by the Free Software Foundation; either version 2
171 * of the License, or (at your option) any later version.
173 * This program is distributed in the hope that it will be useful,
174 * but WITHOUT ANY WARRANTY; without even the implied warranty of
175 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
176 * GNU General Public License for more details.
180 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
183 CREATE TABLE actor.stat_cat (
184 id SERIAL PRIMARY KEY,
187 opac_visible BOOL NOT NULL DEFAULT FALSE,
188 usr_summary BOOL NOT NULL DEFAULT FALSE,
189 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
191 COMMENT ON TABLE actor.stat_cat IS $$
193 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
194 * Mike Rylander <mrylander@gmail.com>
196 * User Statistical Catagories
198 * Local data collected about Users is placed into a Statistical
199 * Catagory. Here's where those catagories are defined.
203 * This program is free software; you can redistribute it and/or
204 * modify it under the terms of the GNU General Public License
205 * as published by the Free Software Foundation; either version 2
206 * of the License, or (at your option) any later version.
208 * This program is distributed in the hope that it will be useful,
209 * but WITHOUT ANY WARRANTY; without even the implied warranty of
210 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
211 * GNU General Public License for more details.
216 CREATE TABLE actor.stat_cat_entry (
217 id SERIAL PRIMARY KEY,
218 stat_cat INT NOT NULL,
221 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
223 COMMENT ON TABLE actor.stat_cat_entry IS $$
225 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
226 * Mike Rylander <mrylander@gmail.com>
228 * User Statistical Catagory Entries
230 * Local data collected about Users is placed into a Statistical
231 * Catagory. Each library can create entries into any of its own
232 * stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
237 * This program is free software; you can redistribute it and/or
238 * modify it under the terms of the GNU General Public License
239 * as published by the Free Software Foundation; either version 2
240 * of the License, or (at your option) any later version.
242 * This program is distributed in the hope that it will be useful,
243 * but WITHOUT ANY WARRANTY; without even the implied warranty of
244 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
245 * GNU General Public License for more details.
250 CREATE TABLE actor.stat_cat_entry_usr_map (
251 id BIGSERIAL PRIMARY KEY,
252 stat_cat_entry TEXT NOT NULL,
253 stat_cat INT NOT NULL,
254 target_usr INT NOT NULL,
255 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
257 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
259 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
260 * Mike Rylander <mrylander@gmail.com>
262 * Statistical Catagory Entry to User map
264 * Records the stat_cat entries for each user.
269 * This program is free software; you can redistribute it and/or
270 * modify it under the terms of the GNU General Public License
271 * as published by the Free Software Foundation; either version 2
272 * of the License, or (at your option) any later version.
274 * This program is distributed in the hope that it will be useful,
275 * but WITHOUT ANY WARRANTY; without even the implied warranty of
276 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
277 * GNU General Public License for more details.
281 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
283 CREATE TABLE actor.card (
284 id SERIAL PRIMARY KEY,
285 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
286 barcode TEXT NOT NULL UNIQUE,
287 active BOOL NOT NULL DEFAULT TRUE
289 COMMENT ON TABLE actor.card IS $$
291 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
292 * Mike Rylander <mrylander@gmail.com>
296 * Each User has one or more library cards. The current "main"
297 * card is linked to here from the actor.usr table, and it is up
298 * to the consortium policy whether more than one card can be
299 * active for any one user at a given time.
304 * This program is free software; you can redistribute it and/or
305 * modify it under the terms of the GNU General Public License
306 * as published by the Free Software Foundation; either version 2
307 * of the License, or (at your option) any later version.
309 * This program is distributed in the hope that it will be useful,
310 * but WITHOUT ANY WARRANTY; without even the implied warranty of
311 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
312 * GNU General Public License for more details.
316 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
318 CREATE TABLE actor.org_unit_type (
319 id SERIAL PRIMARY KEY,
321 opac_label TEXT NOT NULL,
323 parent INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
324 can_have_vols BOOL NOT NULL DEFAULT TRUE,
325 can_have_users BOOL NOT NULL DEFAULT TRUE
327 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
329 CREATE TABLE actor.org_unit (
330 id SERIAL PRIMARY KEY,
331 parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
332 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED,
337 shortname TEXT NOT NULL UNIQUE,
338 name TEXT NOT NULL UNIQUE,
341 opac_visible BOOL NOT NULL DEFAULT TRUE
343 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
344 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
345 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
346 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
347 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
348 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
350 CREATE TABLE actor.org_lasso (
351 id SERIAL PRIMARY KEY,
355 CREATE TABLE actor.org_lasso_map (
356 id SERIAL PRIMARY KEY,
357 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
358 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
360 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
361 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
363 CREATE TABLE actor.org_unit_proximity (
364 id BIGSERIAL PRIMARY KEY,
369 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
371 CREATE TABLE actor.hours_of_operation (
372 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
373 dow_0_open TIME NOT NULL DEFAULT '09:00',
374 dow_0_close TIME NOT NULL DEFAULT '17:00',
375 dow_1_open TIME NOT NULL DEFAULT '09:00',
376 dow_1_close TIME NOT NULL DEFAULT '17:00',
377 dow_2_open TIME NOT NULL DEFAULT '09:00',
378 dow_2_close TIME NOT NULL DEFAULT '17:00',
379 dow_3_open TIME NOT NULL DEFAULT '09:00',
380 dow_3_close TIME NOT NULL DEFAULT '17:00',
381 dow_4_open TIME NOT NULL DEFAULT '09:00',
382 dow_4_close TIME NOT NULL DEFAULT '17:00',
383 dow_5_open TIME NOT NULL DEFAULT '09:00',
384 dow_5_close TIME NOT NULL DEFAULT '17:00',
385 dow_6_open TIME NOT NULL DEFAULT '09:00',
386 dow_6_close TIME NOT NULL DEFAULT '17:00'
389 CREATE TABLE actor.org_unit_closed (
390 id SERIAL PRIMARY KEY,
391 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
392 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
393 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
397 -- Workstation registration...
398 CREATE TABLE actor.workstation (
399 id SERIAL PRIMARY KEY,
400 name TEXT NOT NULL UNIQUE,
401 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
404 CREATE TABLE actor.usr_org_unit_opt_in (
405 id SERIAL PRIMARY KEY,
406 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
407 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
408 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
409 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
410 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
411 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
413 CREATE INDEX usr_org_unit_opt_in_staff_idx ON actor.usr_org_unit_opt_in ( staff );
415 CREATE TABLE actor.org_unit_setting (
416 id BIGSERIAL PRIMARY KEY,
417 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
418 name TEXT NOT NULL REFERENCES config.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
420 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
422 COMMENT ON TABLE actor.org_unit_setting IS $$
424 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
425 * Mike Rylander <mrylander@gmail.com>
429 * This table contains any arbitrary settings that a client
430 * program would like to save for an org unit.
434 * This program is free software; you can redistribute it and/or
435 * modify it under the terms of the GNU General Public License
436 * as published by the Free Software Foundation; either version 2
437 * of the License, or (at your option) any later version.
439 * This program is distributed in the hope that it will be useful,
440 * but WITHOUT ANY WARRANTY; without even the implied warranty of
441 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
442 * GNU General Public License for more details.
446 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
449 CREATE TABLE actor.usr_address (
450 id SERIAL PRIMARY KEY,
451 valid BOOL NOT NULL DEFAULT TRUE,
452 within_city_limits BOOL NOT NULL DEFAULT TRUE,
453 address_type TEXT NOT NULL DEFAULT 'MAILING',
454 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
455 street1 TEXT NOT NULL,
460 country TEXT NOT NULL,
461 post_code TEXT NOT NULL,
462 pending BOOL NOT NULL DEFAULT FALSE,
463 replaces INT REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED
466 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
468 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
469 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
471 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
472 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
473 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
476 CREATE TABLE actor.org_address (
477 id SERIAL PRIMARY KEY,
478 valid BOOL NOT NULL DEFAULT TRUE,
479 address_type TEXT NOT NULL DEFAULT 'MAILING',
480 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
481 street1 TEXT NOT NULL,
486 country TEXT NOT NULL,
487 post_code TEXT NOT NULL
490 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
492 CREATE OR REPLACE FUNCTION public.first5 ( TEXT ) RETURNS TEXT AS $$
493 SELECT SUBSTRING( $1, 1, 5);
496 CREATE TABLE actor.usr_standing_penalty (
497 id SERIAL PRIMARY KEY,
498 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
499 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
500 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
501 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
502 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
503 stop_date TIMESTAMP WITH TIME ZONE,
506 COMMENT ON TABLE actor.usr_standing_penalty IS $$
508 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
509 * Mike Rylander <mrylander@gmail.com>
511 * User standing penalties
515 * This program is free software; you can redistribute it and/or
516 * modify it under the terms of the GNU General Public License
517 * as published by the Free Software Foundation; either version 2
518 * of the License, or (at your option) any later version.
520 * This program is distributed in the hope that it will be useful,
521 * but WITHOUT ANY WARRANTY; without even the implied warranty of
522 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
523 * GNU General Public License for more details.
527 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
528 CREATE INDEX actor_usr_standing_penalty_staff_idx ON actor.usr_standing_penalty ( staff );