1 DROP SCHEMA actor CASCADE;
5 COMMENT ON SCHEMA actor IS $$
7 * Copyright (C) 2005 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 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 -- Just so that there is a user...
136 INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value, home_ou )
137 VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', 'System Account', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 );
139 CREATE TABLE actor.usr_note (
140 id BIGSERIAL PRIMARY KEY,
141 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
142 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
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);
150 CREATE TABLE actor.usr_standing_penalty (
151 id SERIAL PRIMARY KEY,
152 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
153 penalty_type TEXT NOT NULL
155 COMMENT ON TABLE actor.usr_standing_penalty IS $$
157 * Copyright (C) 2005 Georgia Public Library Service
158 * Mike Rylander <mrylander@gmail.com>
160 * User standing penalties
164 * This program is free software; you can redistribute it and/or
165 * modify it under the terms of the GNU General Public License
166 * as published by the Free Software Foundation; either version 2
167 * of the License, or (at your option) any later version.
169 * This program is distributed in the hope that it will be useful,
170 * but WITHOUT ANY WARRANTY; without even the implied warranty of
171 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
172 * GNU General Public License for more details.
176 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
178 CREATE TABLE actor.usr_setting (
179 id BIGSERIAL PRIMARY KEY,
180 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
183 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
185 COMMENT ON TABLE actor.usr_setting IS $$
187 * Copyright (C) 2005 Georgia Public Library Service
188 * Mike Rylander <mrylander@gmail.com>
192 * This table contains any arbitrary settings that a client
193 * program would like to save for a user.
197 * This program is free software; you can redistribute it and/or
198 * modify it under the terms of the GNU General Public License
199 * as published by the Free Software Foundation; either version 2
200 * of the License, or (at your option) any later version.
202 * This program is distributed in the hope that it will be useful,
203 * but WITHOUT ANY WARRANTY; without even the implied warranty of
204 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
205 * GNU General Public License for more details.
209 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
212 CREATE TABLE actor.stat_cat (
213 id SERIAL PRIMARY KEY,
216 opac_visible BOOL NOT NULL DEFAULT FALSE,
217 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
219 COMMENT ON TABLE actor.stat_cat IS $$
221 * Copyright (C) 2005 Georgia Public Library Service
222 * Mike Rylander <mrylander@gmail.com>
224 * User Statistical Catagories
226 * Local data collected about Users is placed into a Statistical
227 * Catagory. Here's where those catagories are defined.
231 * This program is free software; you can redistribute it and/or
232 * modify it under the terms of the GNU General Public License
233 * as published by the Free Software Foundation; either version 2
234 * of the License, or (at your option) any later version.
236 * This program is distributed in the hope that it will be useful,
237 * but WITHOUT ANY WARRANTY; without even the implied warranty of
238 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
239 * GNU General Public License for more details.
244 CREATE TABLE actor.stat_cat_entry (
245 id SERIAL PRIMARY KEY,
246 stat_cat INT NOT NULL,
249 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
251 COMMENT ON TABLE actor.stat_cat_entry IS $$
253 * Copyright (C) 2005 Georgia Public Library Service
254 * Mike Rylander <mrylander@gmail.com>
256 * User Statistical Catagory Entries
258 * Local data collected about Users is placed into a Statistical
259 * Catagory. Each library can create entries into any of it's own
260 * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
265 * This program is free software; you can redistribute it and/or
266 * modify it under the terms of the GNU General Public License
267 * as published by the Free Software Foundation; either version 2
268 * of the License, or (at your option) any later version.
270 * This program is distributed in the hope that it will be useful,
271 * but WITHOUT ANY WARRANTY; without even the implied warranty of
272 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
273 * GNU General Public License for more details.
278 CREATE TABLE actor.stat_cat_entry_usr_map (
279 id BIGSERIAL PRIMARY KEY,
280 stat_cat_entry TEXT NOT NULL,
281 stat_cat INT NOT NULL,
282 target_usr INT NOT NULL,
283 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
285 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
287 * Copyright (C) 2005 Georgia Public Library Service
288 * Mike Rylander <mrylander@gmail.com>
290 * Statistical Catagory Entry to User map
292 * Records the stat_cat entries for each user.
297 * This program is free software; you can redistribute it and/or
298 * modify it under the terms of the GNU General Public License
299 * as published by the Free Software Foundation; either version 2
300 * of the License, or (at your option) any later version.
302 * This program is distributed in the hope that it will be useful,
303 * but WITHOUT ANY WARRANTY; without even the implied warranty of
304 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
305 * GNU General Public License for more details.
309 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
311 CREATE TABLE actor.card (
312 id SERIAL PRIMARY KEY,
313 usr INT NOT NULL REFERENCES actor.usr (id),
314 barcode TEXT NOT NULL UNIQUE,
315 active BOOL NOT NULL DEFAULT TRUE
317 COMMENT ON TABLE actor.card IS $$
319 * Copyright (C) 2005 Georgia Public Library Service
320 * Mike Rylander <mrylander@gmail.com>
324 * Each User has one or more library cards. The current "main"
325 * card is linked to here from the actor.usr table, and it is up
326 * to the consortium policy whether more than one card can be
327 * active for any one user at a given time.
332 * This program is free software; you can redistribute it and/or
333 * modify it under the terms of the GNU General Public License
334 * as published by the Free Software Foundation; either version 2
335 * of the License, or (at your option) any later version.
337 * This program is distributed in the hope that it will be useful,
338 * but WITHOUT ANY WARRANTY; without even the implied warranty of
339 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
340 * GNU General Public License for more details.
344 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
346 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
349 CREATE TABLE actor.org_unit_type (
350 id SERIAL PRIMARY KEY,
352 opac_label TEXT NOT NULL,
354 parent INT REFERENCES actor.org_unit_type (id),
355 can_have_vols BOOL NOT NULL DEFAULT TRUE,
356 can_have_users BOOL NOT NULL DEFAULT TRUE
358 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
361 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE );
362 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'System','Local Library System', 1, 1, FALSE, FALSE );
363 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
364 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
365 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
367 CREATE TABLE actor.org_unit (
368 id SERIAL PRIMARY KEY,
369 parent_ou INT REFERENCES actor.org_unit (id),
370 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id),
375 shortname TEXT NOT NULL,
380 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
381 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
382 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
383 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
384 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
385 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
387 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'CONS', 'Example Consortium');
388 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS1', 'Example System 1');
389 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS2', 'Example System 2');
390 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR1', 'Example Branch 1');
391 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2');
392 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3');
393 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4');
394 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL1', 'Example Sub-lib 1');
395 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM1', 'Example Bookmobile 1');
397 CREATE TABLE actor.org_unit_proximity (
398 id BIGSERIAL PRIMARY KEY,
403 CREATE INDEX from_prox_idx ON actor.org_unit_proximity (from_org);
405 CREATE TABLE actor.hours_of_operation (
406 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE,
407 dow_0_open TIME NOT NULL DEFAULT '09:00',
408 dow_0_close TIME NOT NULL DEFAULT '17:00',
409 dow_1_open TIME NOT NULL DEFAULT '09:00',
410 dow_1_close TIME NOT NULL DEFAULT '17:00',
411 dow_2_open TIME NOT NULL DEFAULT '09:00',
412 dow_2_close TIME NOT NULL DEFAULT '17:00',
413 dow_3_open TIME NOT NULL DEFAULT '09:00',
414 dow_3_close TIME NOT NULL DEFAULT '17:00',
415 dow_4_open TIME NOT NULL DEFAULT '09:00',
416 dow_4_close TIME NOT NULL DEFAULT '17:00',
417 dow_5_open TIME NOT NULL DEFAULT '09:00',
418 dow_5_close TIME NOT NULL DEFAULT '17:00',
419 dow_6_open TIME NOT NULL DEFAULT '09:00',
420 dow_6_close TIME NOT NULL DEFAULT '17:00'
423 CREATE TABLE actor.org_unit_closed (
424 id SERIAL PRIMARY KEY,
425 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
426 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
427 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
431 -- Workstation registration...
432 CREATE TABLE actor.workstation (
433 id SERIAL PRIMARY KEY,
434 name TEXT NOT NULL UNIQUE,
435 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
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 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);
513 INSERT INTO actor.org_address VALUES (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303');
514 UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1;