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 usrgroup SERIAL NOT NULL,
59 claims_returned_count INT NOT NULL DEFAULT 0,
60 credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00,
61 last_xact_id TEXT NOT NULL DEFAULT 'none',
63 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
64 expire_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + '3 years'::INTERVAL)
66 COMMENT ON TABLE actor.usr IS $$
68 * Copyright (C) 2005 Georgia Public Library Service
69 * Mike Rylander <mrylander@gmail.com>
73 * This table contains the core User objects that describe both
74 * staff members and patrons. The difference between the two
75 * types of users is based on the user's permissions.
79 * This program is free software; you can redistribute it and/or
80 * modify it under the terms of the GNU General Public License
81 * as published by the Free Software Foundation; either version 2
82 * of the License, or (at your option) any later version.
84 * This program is distributed in the hope that it will be useful,
85 * but WITHOUT ANY WARRANTY; without even the implied warranty of
86 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
87 * GNU General Public License for more details.
91 CREATE UNIQUE INDEX actor_usr_unique_ident ON actor.usr (ident_type, ident_value);
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 -- Just so that there is a user...
135 INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value, home_ou )
136 VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', 'System Account', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 );
138 CREATE TABLE actor.usr_note (
139 id BIGSERIAL PRIMARY KEY,
140 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
141 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
142 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
143 pub BOOL NOT NULL DEFAULT FALSE,
147 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
149 CREATE TABLE actor.usr_standing_penalty (
150 id SERIAL PRIMARY KEY,
151 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
152 penalty_type TEXT NOT NULL
154 COMMENT ON TABLE actor.usr_standing_penalty IS $$
156 * Copyright (C) 2005 Georgia Public Library Service
157 * Mike Rylander <mrylander@gmail.com>
159 * User standing penalties
163 * This program is free software; you can redistribute it and/or
164 * modify it under the terms of the GNU General Public License
165 * as published by the Free Software Foundation; either version 2
166 * of the License, or (at your option) any later version.
168 * This program is distributed in the hope that it will be useful,
169 * but WITHOUT ANY WARRANTY; without even the implied warranty of
170 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
171 * GNU General Public License for more details.
175 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
177 CREATE TABLE actor.usr_setting (
178 id BIGSERIAL PRIMARY KEY,
179 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
182 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
184 COMMENT ON TABLE actor.usr_setting IS $$
186 * Copyright (C) 2005 Georgia Public Library Service
187 * Mike Rylander <mrylander@gmail.com>
191 * This table contains any arbitrary settings that a client
192 * program would like to save for a user.
196 * This program is free software; you can redistribute it and/or
197 * modify it under the terms of the GNU General Public License
198 * as published by the Free Software Foundation; either version 2
199 * of the License, or (at your option) any later version.
201 * This program is distributed in the hope that it will be useful,
202 * but WITHOUT ANY WARRANTY; without even the implied warranty of
203 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
204 * GNU General Public License for more details.
208 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
211 CREATE TABLE actor.stat_cat (
212 id SERIAL PRIMARY KEY,
215 opac_visible BOOL NOT NULL DEFAULT FALSE,
216 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
218 COMMENT ON TABLE actor.stat_cat IS $$
220 * Copyright (C) 2005 Georgia Public Library Service
221 * Mike Rylander <mrylander@gmail.com>
223 * User Statistical Catagories
225 * Local data collected about Users is placed into a Statistical
226 * Catagory. Here's where those catagories are defined.
230 * This program is free software; you can redistribute it and/or
231 * modify it under the terms of the GNU General Public License
232 * as published by the Free Software Foundation; either version 2
233 * of the License, or (at your option) any later version.
235 * This program is distributed in the hope that it will be useful,
236 * but WITHOUT ANY WARRANTY; without even the implied warranty of
237 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
238 * GNU General Public License for more details.
243 CREATE TABLE actor.stat_cat_entry (
244 id SERIAL PRIMARY KEY,
245 stat_cat INT NOT NULL,
248 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
250 COMMENT ON TABLE actor.stat_cat_entry IS $$
252 * Copyright (C) 2005 Georgia Public Library Service
253 * Mike Rylander <mrylander@gmail.com>
255 * User Statistical Catagory Entries
257 * Local data collected about Users is placed into a Statistical
258 * Catagory. Each library can create entries into any of it's own
259 * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
264 * This program is free software; you can redistribute it and/or
265 * modify it under the terms of the GNU General Public License
266 * as published by the Free Software Foundation; either version 2
267 * of the License, or (at your option) any later version.
269 * This program is distributed in the hope that it will be useful,
270 * but WITHOUT ANY WARRANTY; without even the implied warranty of
271 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
272 * GNU General Public License for more details.
277 CREATE TABLE actor.stat_cat_entry_usr_map (
278 id BIGSERIAL PRIMARY KEY,
279 stat_cat_entry TEXT NOT NULL,
280 stat_cat INT NOT NULL,
281 target_usr INT NOT NULL,
282 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
284 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
286 * Copyright (C) 2005 Georgia Public Library Service
287 * Mike Rylander <mrylander@gmail.com>
289 * Statistical Catagory Entry to User map
291 * Records the stat_cat entries for each user.
296 * This program is free software; you can redistribute it and/or
297 * modify it under the terms of the GNU General Public License
298 * as published by the Free Software Foundation; either version 2
299 * of the License, or (at your option) any later version.
301 * This program is distributed in the hope that it will be useful,
302 * but WITHOUT ANY WARRANTY; without even the implied warranty of
303 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
304 * GNU General Public License for more details.
308 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
310 CREATE TABLE actor.card (
311 id SERIAL PRIMARY KEY,
312 usr INT NOT NULL REFERENCES actor.usr (id),
313 barcode TEXT NOT NULL UNIQUE,
314 active BOOL NOT NULL DEFAULT TRUE
316 COMMENT ON TABLE actor.card IS $$
318 * Copyright (C) 2005 Georgia Public Library Service
319 * Mike Rylander <mrylander@gmail.com>
323 * Each User has one or more library cards. The current "main"
324 * card is linked to here from the actor.usr table, and it is up
325 * to the consortium policy whether more than one card can be
326 * active for any one user at a given time.
331 * This program is free software; you can redistribute it and/or
332 * modify it under the terms of the GNU General Public License
333 * as published by the Free Software Foundation; either version 2
334 * of the License, or (at your option) any later version.
336 * This program is distributed in the hope that it will be useful,
337 * but WITHOUT ANY WARRANTY; without even the implied warranty of
338 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
339 * GNU General Public License for more details.
343 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
345 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
348 CREATE TABLE actor.org_unit_type (
349 id SERIAL PRIMARY KEY,
351 opac_label TEXT NOT NULL,
353 parent INT REFERENCES actor.org_unit_type (id),
354 can_have_vols BOOL NOT NULL DEFAULT TRUE,
355 can_have_users BOOL NOT NULL DEFAULT TRUE
357 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
360 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE );
361 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 );
362 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
363 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
364 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
366 CREATE TABLE actor.org_unit (
367 id SERIAL PRIMARY KEY,
368 parent_ou INT REFERENCES actor.org_unit (id),
369 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id),
374 shortname TEXT NOT NULL,
377 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
378 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
379 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
380 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
381 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
382 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
384 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'CONS', 'Example Consortium');
385 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS1', 'Example System 1');
386 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS2', 'Example System 2');
387 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR1', 'Example Branch 1');
388 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2');
389 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3');
390 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4');
391 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL1', 'Example Sub-lib 1');
392 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM1', 'Example Bookmobile 1');
394 CREATE TABLE actor.hours_of_operation (
395 id INT PRIMARY KEY REFERENCES actor.org_unit (id) ON DELETE CASCADE,
396 dow_0_open TIME NOT NULL DEFAULT '09:00',
397 dow_0_close TIME NOT NULL DEFAULT '17:00',
398 dow_1_open TIME NOT NULL DEFAULT '09:00',
399 dow_1_close TIME NOT NULL DEFAULT '17:00',
400 dow_2_open TIME NOT NULL DEFAULT '09:00',
401 dow_2_close TIME NOT NULL DEFAULT '17:00',
402 dow_3_open TIME NOT NULL DEFAULT '09:00',
403 dow_3_close TIME NOT NULL DEFAULT '17:00',
404 dow_4_open TIME NOT NULL DEFAULT '09:00',
405 dow_4_close TIME NOT NULL DEFAULT '17:00',
406 dow_5_open TIME NOT NULL DEFAULT '09:00',
407 dow_5_close TIME NOT NULL DEFAULT '17:00',
408 dow_6_open TIME NOT NULL DEFAULT '09:00',
409 dow_6_close TIME NOT NULL DEFAULT '17:00'
412 CREATE TABLE actor.org_unit_closed (
413 id SERIAL PRIMARY KEY,
414 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
415 close_start TIMESTAMP WITH TIME ZONE NOT NULL,
416 close_end TIMESTAMP WITH TIME ZONE NOT NULL,
420 -- Workstation registration...
421 CREATE TABLE actor.workstation (
422 id SERIAL PRIMARY KEY,
423 name TEXT NOT NULL UNIQUE,
424 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
427 CREATE TABLE actor.org_unit_setting (
428 id BIGSERIAL PRIMARY KEY,
429 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
432 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
434 COMMENT ON TABLE actor.org_unit_setting IS $$
436 * Copyright (C) 2005 Georgia Public Library Service
437 * Mike Rylander <mrylander@gmail.com>
441 * This table contains any arbitrary settings that a client
442 * program would like to save for an org unit.
446 * This program is free software; you can redistribute it and/or
447 * modify it under the terms of the GNU General Public License
448 * as published by the Free Software Foundation; either version 2
449 * of the License, or (at your option) any later version.
451 * This program is distributed in the hope that it will be useful,
452 * but WITHOUT ANY WARRANTY; without even the implied warranty of
453 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
454 * GNU General Public License for more details.
458 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
461 CREATE TABLE actor.usr_address (
462 id SERIAL PRIMARY KEY,
463 valid BOOL NOT NULL DEFAULT TRUE,
464 within_city_limits BOOL NOT NULL DEFAULT TRUE,
465 address_type TEXT NOT NULL DEFAULT 'MAILING',
466 usr INT NOT NULL REFERENCES actor.usr (id),
467 street1 TEXT NOT NULL,
472 country TEXT NOT NULL,
473 post_code TEXT NOT NULL
476 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
478 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
479 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
481 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
482 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
483 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
486 CREATE TABLE actor.org_address (
487 id SERIAL PRIMARY KEY,
488 valid BOOL NOT NULL DEFAULT TRUE,
489 address_type TEXT NOT NULL DEFAULT 'MAILING',
490 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
491 street1 TEXT NOT NULL,
496 country TEXT NOT NULL,
497 post_code TEXT NOT NULL
500 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
502 INSERT INTO actor.org_address VALUES (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303');
503 UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1;