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),
36 ident_type INT NOT NULL REFERENCES config.identification_type (id),
38 ident_type2 INT REFERENCES config.identification_type (id),
40 net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id),
43 first_given_name TEXT NOT NULL,
44 second_given_name TEXT,
45 family_name TEXT NOT NULL,
53 dob TIMESTAMP WITH TIME ZONE,
54 active BOOL NOT NULL DEFAULT TRUE,
55 master_account BOOL NOT NULL DEFAULT FALSE,
56 super_user BOOL NOT NULL DEFAULT FALSE,
57 barred BOOL NOT NULL DEFAULT FALSE,
58 deleted 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)
67 COMMENT ON TABLE actor.usr IS $$
69 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
70 * Mike Rylander <mrylander@gmail.com>
74 * This table contains the core User objects that describe both
75 * staff members and patrons. The difference between the two
76 * types of users is based on the user's permissions.
80 * This program is free software; you can redistribute it and/or
81 * modify it under the terms of the GNU General Public License
82 * as published by the Free Software Foundation; either version 2
83 * of the License, or (at your option) any later version.
85 * This program is distributed in the hope that it will be useful,
86 * but WITHOUT ANY WARRANTY; without even the implied warranty of
87 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
88 * GNU General Public License for more details.
92 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
93 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
94 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
96 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name));
97 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name));
98 CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name));
100 CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email));
102 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone));
103 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone));
104 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone));
106 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value));
107 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2));
109 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
111 NEW.passwd = MD5( NEW.passwd );
116 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
118 IF NEW.passwd <> OLD.passwd THEN
119 NEW.passwd = MD5( NEW.passwd );
125 CREATE TRIGGER actor_crypt_pw_update_trigger
126 BEFORE UPDATE ON actor.usr FOR EACH ROW
127 EXECUTE PROCEDURE actor.crypt_pw_update ();
129 CREATE TRIGGER actor_crypt_pw_insert_trigger
130 BEFORE INSERT ON actor.usr FOR EACH ROW
131 EXECUTE PROCEDURE actor.crypt_pw_insert ();
133 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;
135 CREATE TABLE actor.usr_note (
136 id BIGSERIAL PRIMARY KEY,
137 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
138 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
139 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
140 pub BOOL NOT NULL DEFAULT FALSE,
144 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
146 CREATE TABLE actor.usr_standing_penalty (
147 id SERIAL PRIMARY KEY,
148 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
149 penalty_type TEXT NOT NULL
151 COMMENT ON TABLE actor.usr_standing_penalty IS $$
153 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
154 * Mike Rylander <mrylander@gmail.com>
156 * User standing penalties
160 * This program is free software; you can redistribute it and/or
161 * modify it under the terms of the GNU General Public License
162 * as published by the Free Software Foundation; either version 2
163 * of the License, or (at your option) any later version.
165 * This program is distributed in the hope that it will be useful,
166 * but WITHOUT ANY WARRANTY; without even the implied warranty of
167 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
168 * GNU General Public License for more details.
172 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
174 CREATE TABLE actor.usr_setting (
175 id BIGSERIAL PRIMARY KEY,
176 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
179 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
181 COMMENT ON TABLE actor.usr_setting IS $$
183 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
184 * Mike Rylander <mrylander@gmail.com>
188 * This table contains any arbitrary settings that a client
189 * program would like to save for a user.
193 * This program is free software; you can redistribute it and/or
194 * modify it under the terms of the GNU General Public License
195 * as published by the Free Software Foundation; either version 2
196 * of the License, or (at your option) any later version.
198 * This program is distributed in the hope that it will be useful,
199 * but WITHOUT ANY WARRANTY; without even the implied warranty of
200 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
201 * GNU General Public License for more details.
205 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
208 CREATE TABLE actor.stat_cat (
209 id SERIAL PRIMARY KEY,
212 opac_visible BOOL NOT NULL DEFAULT FALSE,
213 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
215 COMMENT ON TABLE actor.stat_cat IS $$
217 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
218 * Mike Rylander <mrylander@gmail.com>
220 * User Statistical Catagories
222 * Local data collected about Users is placed into a Statistical
223 * Catagory. Here's where those catagories are defined.
227 * This program is free software; you can redistribute it and/or
228 * modify it under the terms of the GNU General Public License
229 * as published by the Free Software Foundation; either version 2
230 * of the License, or (at your option) any later version.
232 * This program is distributed in the hope that it will be useful,
233 * but WITHOUT ANY WARRANTY; without even the implied warranty of
234 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
235 * GNU General Public License for more details.
240 CREATE TABLE actor.stat_cat_entry (
241 id SERIAL PRIMARY KEY,
242 stat_cat INT NOT NULL,
245 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
247 COMMENT ON TABLE actor.stat_cat_entry IS $$
249 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
250 * Mike Rylander <mrylander@gmail.com>
252 * User Statistical Catagory Entries
254 * Local data collected about Users is placed into a Statistical
255 * Catagory. Each library can create entries into any of its own
256 * stat_cats, its ancestors' stat_cats, or its descendants' stat_cats.
261 * This program is free software; you can redistribute it and/or
262 * modify it under the terms of the GNU General Public License
263 * as published by the Free Software Foundation; either version 2
264 * of the License, or (at your option) any later version.
266 * This program is distributed in the hope that it will be useful,
267 * but WITHOUT ANY WARRANTY; without even the implied warranty of
268 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
269 * GNU General Public License for more details.
274 CREATE TABLE actor.stat_cat_entry_usr_map (
275 id BIGSERIAL PRIMARY KEY,
276 stat_cat_entry TEXT NOT NULL,
277 stat_cat INT NOT NULL,
278 target_usr INT NOT NULL,
279 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
281 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
283 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
284 * Mike Rylander <mrylander@gmail.com>
286 * Statistical Catagory Entry to User map
288 * Records the stat_cat entries for each user.
293 * This program is free software; you can redistribute it and/or
294 * modify it under the terms of the GNU General Public License
295 * as published by the Free Software Foundation; either version 2
296 * of the License, or (at your option) any later version.
298 * This program is distributed in the hope that it will be useful,
299 * but WITHOUT ANY WARRANTY; without even the implied warranty of
300 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
301 * GNU General Public License for more details.
305 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
307 CREATE TABLE actor.card (
308 id SERIAL PRIMARY KEY,
309 usr INT NOT NULL REFERENCES actor.usr (id),
310 barcode TEXT NOT NULL UNIQUE,
311 active BOOL NOT NULL DEFAULT TRUE
313 COMMENT ON TABLE actor.card IS $$
315 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
316 * Mike Rylander <mrylander@gmail.com>
320 * Each User has one or more library cards. The current "main"
321 * card is linked to here from the actor.usr table, and it is up
322 * to the consortium policy whether more than one card can be
323 * active for any one user at a given time.
328 * This program is free software; you can redistribute it and/or
329 * modify it under the terms of the GNU General Public License
330 * as published by the Free Software Foundation; either version 2
331 * of the License, or (at your option) any later version.
333 * This program is distributed in the hope that it will be useful,
334 * but WITHOUT ANY WARRANTY; without even the implied warranty of
335 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
336 * GNU General Public License for more details.
340 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
342 CREATE TABLE actor.org_unit_type (
343 id SERIAL PRIMARY KEY,
345 opac_label TEXT NOT NULL,
347 parent INT REFERENCES actor.org_unit_type (id),
348 can_have_vols BOOL NOT NULL DEFAULT TRUE,
349 can_have_users BOOL NOT NULL DEFAULT TRUE
351 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
353 CREATE TABLE actor.org_unit (
354 id SERIAL PRIMARY KEY,
355 parent_ou INT REFERENCES actor.org_unit (id),
356 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id),
361 shortname TEXT NOT NULL,
365 opac_visible BOOL NOT NULL DEFAULT TRUE
367 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
368 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
369 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
370 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
371 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
372 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
374 CREATE TABLE actor.org_lasso (
375 id SERIAL PRIMARY KEY,
379 CREATE TABLE actor.org_lasso_map (
380 id SERIAL PRIMARY KEY,
381 lasso INT NOT NULL REFERENCES actor.org_lasso (id) ON DELETE CASCADE,
382 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE
384 CREATE UNIQUE INDEX ou_lasso_lasso_ou_idx ON actor.org_lasso_map (lasso, org_unit);
385 CREATE INDEX ou_lasso_org_unit_idx ON actor.org_lasso_map (org_unit);
387 CREATE TABLE actor.org_unit_proximity (
388 id BIGSERIAL PRIMARY KEY,
393 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
395 CREATE TABLE actor.hours_of_operation (
396 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE,
397 dow_0_open TIME NOT NULL DEFAULT '09:00',
398 dow_0_close TIME NOT NULL DEFAULT '17:00',
399 dow_1_open TIME NOT NULL DEFAULT '09:00',
400 dow_1_close TIME NOT NULL DEFAULT '17:00',
401 dow_2_open TIME NOT NULL DEFAULT '09:00',
402 dow_2_close TIME NOT NULL DEFAULT '17:00',
403 dow_3_open TIME NOT NULL DEFAULT '09:00',
404 dow_3_close TIME NOT NULL DEFAULT '17:00',
405 dow_4_open TIME NOT NULL DEFAULT '09:00',
406 dow_4_close TIME NOT NULL DEFAULT '17:00',
407 dow_5_open TIME NOT NULL DEFAULT '09:00',
408 dow_5_close TIME NOT NULL DEFAULT '17:00',
409 dow_6_open TIME NOT NULL DEFAULT '09:00',
410 dow_6_close TIME NOT NULL DEFAULT '17:00'
413 CREATE TABLE actor.org_unit_closed (
414 id SERIAL PRIMARY KEY,
415 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
416 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
417 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
421 -- Workstation registration...
422 CREATE TABLE actor.workstation (
423 id SERIAL PRIMARY KEY,
424 name TEXT NOT NULL UNIQUE,
425 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
428 CREATE TABLE actor.usr_org_unit_opt_in (
429 id SERIAL PRIMARY KEY,
430 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
431 usr INT NOT NULL REFERENCES actor.usr (id),
432 staff INT NOT NULL REFERENCES actor.usr (id),
433 opt_in_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
434 opt_in_ws INT NOT NULL REFERENCES actor.workstation (id),
435 CONSTRAINT usr_opt_in_once_per_org_unit UNIQUE (usr,org_unit)
438 CREATE TABLE actor.org_unit_setting (
439 id BIGSERIAL PRIMARY KEY,
440 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
443 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
445 COMMENT ON TABLE actor.org_unit_setting IS $$
447 * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
448 * Mike Rylander <mrylander@gmail.com>
452 * This table contains any arbitrary settings that a client
453 * program would like to save for an org unit.
457 * This program is free software; you can redistribute it and/or
458 * modify it under the terms of the GNU General Public License
459 * as published by the Free Software Foundation; either version 2
460 * of the License, or (at your option) any later version.
462 * This program is distributed in the hope that it will be useful,
463 * but WITHOUT ANY WARRANTY; without even the implied warranty of
464 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
465 * GNU General Public License for more details.
469 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
472 CREATE TABLE actor.usr_address (
473 id SERIAL PRIMARY KEY,
474 valid BOOL NOT NULL DEFAULT TRUE,
475 within_city_limits BOOL NOT NULL DEFAULT TRUE,
476 address_type TEXT NOT NULL DEFAULT 'MAILING',
477 usr INT NOT NULL REFERENCES actor.usr (id),
478 street1 TEXT NOT NULL,
483 country TEXT NOT NULL,
484 post_code TEXT NOT NULL
487 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
489 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
490 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
492 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
493 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
494 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
497 CREATE TABLE actor.org_address (
498 id SERIAL PRIMARY KEY,
499 valid BOOL NOT NULL DEFAULT TRUE,
500 address_type TEXT NOT NULL DEFAULT 'MAILING',
501 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
502 street1 TEXT NOT NULL,
507 country TEXT NOT NULL,
508 post_code TEXT NOT NULL
511 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);