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 NOT NULL,
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 UNIQUE INDEX actor_usr_unique_ident ON actor.usr (ident_type, ident_value);
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 -- Just so that there is a user...
138 INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value, home_ou )
139 VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', 'System Account', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 );
141 CREATE TABLE actor.usr_note (
142 id BIGSERIAL PRIMARY KEY,
143 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
144 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
145 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
146 pub BOOL NOT NULL DEFAULT FALSE,
150 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
152 CREATE TABLE actor.usr_standing_penalty (
153 id SERIAL PRIMARY KEY,
154 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
155 penalty_type TEXT NOT NULL
157 COMMENT ON TABLE actor.usr_standing_penalty IS $$
159 * Copyright (C) 2005 Georgia Public Library Service
160 * Mike Rylander <mrylander@gmail.com>
162 * User standing penalties
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_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
180 CREATE TABLE actor.usr_setting (
181 id BIGSERIAL PRIMARY KEY,
182 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
185 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
187 COMMENT ON TABLE actor.usr_setting IS $$
189 * Copyright (C) 2005 Georgia Public Library Service
190 * Mike Rylander <mrylander@gmail.com>
194 * This table contains any arbitrary settings that a client
195 * program would like to save for a user.
199 * This program is free software; you can redistribute it and/or
200 * modify it under the terms of the GNU General Public License
201 * as published by the Free Software Foundation; either version 2
202 * of the License, or (at your option) any later version.
204 * This program is distributed in the hope that it will be useful,
205 * but WITHOUT ANY WARRANTY; without even the implied warranty of
206 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
207 * GNU General Public License for more details.
211 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
214 CREATE TABLE actor.stat_cat (
215 id SERIAL PRIMARY KEY,
218 opac_visible BOOL NOT NULL DEFAULT FALSE,
219 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
221 COMMENT ON TABLE actor.stat_cat IS $$
223 * Copyright (C) 2005 Georgia Public Library Service
224 * Mike Rylander <mrylander@gmail.com>
226 * User Statistical Catagories
228 * Local data collected about Users is placed into a Statistical
229 * Catagory. Here's where those catagories are defined.
233 * This program is free software; you can redistribute it and/or
234 * modify it under the terms of the GNU General Public License
235 * as published by the Free Software Foundation; either version 2
236 * of the License, or (at your option) any later version.
238 * This program is distributed in the hope that it will be useful,
239 * but WITHOUT ANY WARRANTY; without even the implied warranty of
240 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
241 * GNU General Public License for more details.
246 CREATE TABLE actor.stat_cat_entry (
247 id SERIAL PRIMARY KEY,
248 stat_cat INT NOT NULL,
251 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
253 COMMENT ON TABLE actor.stat_cat_entry IS $$
255 * Copyright (C) 2005 Georgia Public Library Service
256 * Mike Rylander <mrylander@gmail.com>
258 * User Statistical Catagory Entries
260 * Local data collected about Users is placed into a Statistical
261 * Catagory. Each library can create entries into any of it's own
262 * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
267 * This program is free software; you can redistribute it and/or
268 * modify it under the terms of the GNU General Public License
269 * as published by the Free Software Foundation; either version 2
270 * of the License, or (at your option) any later version.
272 * This program is distributed in the hope that it will be useful,
273 * but WITHOUT ANY WARRANTY; without even the implied warranty of
274 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
275 * GNU General Public License for more details.
280 CREATE TABLE actor.stat_cat_entry_usr_map (
281 id BIGSERIAL PRIMARY KEY,
282 stat_cat_entry TEXT NOT NULL,
283 stat_cat INT NOT NULL,
284 target_usr INT NOT NULL,
285 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
287 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
289 * Copyright (C) 2005 Georgia Public Library Service
290 * Mike Rylander <mrylander@gmail.com>
292 * Statistical Catagory Entry to User map
294 * Records the stat_cat entries for each user.
299 * This program is free software; you can redistribute it and/or
300 * modify it under the terms of the GNU General Public License
301 * as published by the Free Software Foundation; either version 2
302 * of the License, or (at your option) any later version.
304 * This program is distributed in the hope that it will be useful,
305 * but WITHOUT ANY WARRANTY; without even the implied warranty of
306 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
307 * GNU General Public License for more details.
311 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
313 CREATE TABLE actor.card (
314 id SERIAL PRIMARY KEY,
315 usr INT NOT NULL REFERENCES actor.usr (id),
316 barcode TEXT NOT NULL UNIQUE,
317 active BOOL NOT NULL DEFAULT TRUE
319 COMMENT ON TABLE actor.card IS $$
321 * Copyright (C) 2005 Georgia Public Library Service
322 * Mike Rylander <mrylander@gmail.com>
326 * Each User has one or more library cards. The current "main"
327 * card is linked to here from the actor.usr table, and it is up
328 * to the consortium policy whether more than one card can be
329 * active for any one user at a given time.
334 * This program is free software; you can redistribute it and/or
335 * modify it under the terms of the GNU General Public License
336 * as published by the Free Software Foundation; either version 2
337 * of the License, or (at your option) any later version.
339 * This program is distributed in the hope that it will be useful,
340 * but WITHOUT ANY WARRANTY; without even the implied warranty of
341 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
342 * GNU General Public License for more details.
346 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
348 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
351 CREATE TABLE actor.org_unit_type (
352 id SERIAL PRIMARY KEY,
354 opac_label TEXT NOT NULL,
356 parent INT REFERENCES actor.org_unit_type (id),
357 can_have_vols BOOL NOT NULL DEFAULT TRUE,
358 can_have_users BOOL NOT NULL DEFAULT TRUE
360 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
363 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE );
364 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 );
365 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
366 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
367 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
369 CREATE TABLE actor.org_unit (
370 id SERIAL PRIMARY KEY,
371 parent_ou INT REFERENCES actor.org_unit (id),
372 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id),
377 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.hours_of_operation (
398 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE,
399 dow_0_open TIME NOT NULL DEFAULT '09:00',
400 dow_0_close TIME NOT NULL DEFAULT '17:00',
401 dow_1_open TIME NOT NULL DEFAULT '09:00',
402 dow_1_close TIME NOT NULL DEFAULT '17:00',
403 dow_2_open TIME NOT NULL DEFAULT '09:00',
404 dow_2_close TIME NOT NULL DEFAULT '17:00',
405 dow_3_open TIME NOT NULL DEFAULT '09:00',
406 dow_3_close TIME NOT NULL DEFAULT '17:00',
407 dow_4_open TIME NOT NULL DEFAULT '09:00',
408 dow_4_close TIME NOT NULL DEFAULT '17:00',
409 dow_5_open TIME NOT NULL DEFAULT '09:00',
410 dow_5_close TIME NOT NULL DEFAULT '17:00',
411 dow_6_open TIME NOT NULL DEFAULT '09:00',
412 dow_6_close TIME NOT NULL DEFAULT '17:00'
415 CREATE TABLE actor.org_unit_closed (
416 id SERIAL PRIMARY KEY,
417 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
418 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
419 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
423 -- Workstation registration...
424 CREATE TABLE actor.workstation (
425 id SERIAL PRIMARY KEY,
426 name TEXT NOT NULL UNIQUE,
427 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
430 CREATE TABLE actor.org_unit_setting (
431 id BIGSERIAL PRIMARY KEY,
432 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
435 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
437 COMMENT ON TABLE actor.org_unit_setting IS $$
439 * Copyright (C) 2005 Georgia Public Library Service
440 * Mike Rylander <mrylander@gmail.com>
444 * This table contains any arbitrary settings that a client
445 * program would like to save for an org unit.
449 * This program is free software; you can redistribute it and/or
450 * modify it under the terms of the GNU General Public License
451 * as published by the Free Software Foundation; either version 2
452 * of the License, or (at your option) any later version.
454 * This program is distributed in the hope that it will be useful,
455 * but WITHOUT ANY WARRANTY; without even the implied warranty of
456 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
457 * GNU General Public License for more details.
461 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
464 CREATE TABLE actor.usr_address (
465 id SERIAL PRIMARY KEY,
466 valid BOOL NOT NULL DEFAULT TRUE,
467 within_city_limits BOOL NOT NULL DEFAULT TRUE,
468 address_type TEXT NOT NULL DEFAULT 'MAILING',
469 usr INT NOT NULL REFERENCES actor.usr (id),
470 street1 TEXT NOT NULL,
475 country TEXT NOT NULL,
476 post_code TEXT NOT NULL
479 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
481 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
482 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
484 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
485 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
486 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
489 CREATE TABLE actor.org_address (
490 id SERIAL PRIMARY KEY,
491 valid BOOL NOT NULL DEFAULT TRUE,
492 address_type TEXT NOT NULL DEFAULT 'MAILING',
493 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
494 street1 TEXT NOT NULL,
499 country TEXT NOT NULL,
500 post_code TEXT NOT NULL
503 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
505 INSERT INTO actor.org_address VALUES (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303');
506 UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1;