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),
37 ident_value TEXT NOT NULL,
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,
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 DATE NOT NULL DEFAULT now()::DATE,
64 expire_date DATE NOT NULL DEFAULT (now() + '3 years'::INTERVAL)::DATE
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 INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
92 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
93 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
95 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name));
96 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name));
97 CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name));
99 CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email));
101 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone));
102 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone));
103 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone));
105 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value));
106 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2));
108 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
110 NEW.passwd = MD5( NEW.passwd );
115 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
117 IF NEW.passwd <> OLD.passwd THEN
118 NEW.passwd = MD5( NEW.passwd );
124 CREATE TRIGGER actor_crypt_pw_update_trigger
125 BEFORE UPDATE ON actor.usr FOR EACH ROW
126 EXECUTE PROCEDURE actor.crypt_pw_update ();
128 CREATE TRIGGER actor_crypt_pw_insert_trigger
129 BEFORE INSERT ON actor.usr FOR EACH ROW
130 EXECUTE PROCEDURE actor.crypt_pw_insert ();
132 -- Just so that there is a user...
133 INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value, home_ou )
134 VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', 'System Account', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 );
136 CREATE TABLE actor.usr_note (
137 id BIGSERIAL PRIMARY KEY,
138 usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
139 creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
140 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
141 pub BOOL NOT NULL DEFAULT FALSE,
145 CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
147 CREATE TABLE actor.usr_standing_penalty (
148 id SERIAL PRIMARY KEY,
149 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
150 penalty_type TEXT NOT NULL
152 COMMENT ON TABLE actor.usr_standing_penalty IS $$
154 * Copyright (C) 2005 Georgia Public Library Service
155 * Mike Rylander <mrylander@gmail.com>
157 * User standing penalties
161 * This program is free software; you can redistribute it and/or
162 * modify it under the terms of the GNU General Public License
163 * as published by the Free Software Foundation; either version 2
164 * of the License, or (at your option) any later version.
166 * This program is distributed in the hope that it will be useful,
167 * but WITHOUT ANY WARRANTY; without even the implied warranty of
168 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
169 * GNU General Public License for more details.
173 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
175 CREATE TABLE actor.usr_setting (
176 id BIGSERIAL PRIMARY KEY,
177 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
180 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
182 COMMENT ON TABLE actor.usr_setting IS $$
184 * Copyright (C) 2005 Georgia Public Library Service
185 * Mike Rylander <mrylander@gmail.com>
189 * This table contains any arbitrary settings that a client
190 * program would like to save for a user.
194 * This program is free software; you can redistribute it and/or
195 * modify it under the terms of the GNU General Public License
196 * as published by the Free Software Foundation; either version 2
197 * of the License, or (at your option) any later version.
199 * This program is distributed in the hope that it will be useful,
200 * but WITHOUT ANY WARRANTY; without even the implied warranty of
201 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
202 * GNU General Public License for more details.
206 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
209 CREATE TABLE actor.stat_cat (
210 id SERIAL PRIMARY KEY,
213 opac_visible BOOL NOT NULL DEFAULT FALSE,
214 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
216 COMMENT ON TABLE actor.stat_cat IS $$
218 * Copyright (C) 2005 Georgia Public Library Service
219 * Mike Rylander <mrylander@gmail.com>
221 * User Statistical Catagories
223 * Local data collected about Users is placed into a Statistical
224 * Catagory. Here's where those catagories are defined.
228 * This program is free software; you can redistribute it and/or
229 * modify it under the terms of the GNU General Public License
230 * as published by the Free Software Foundation; either version 2
231 * of the License, or (at your option) any later version.
233 * This program is distributed in the hope that it will be useful,
234 * but WITHOUT ANY WARRANTY; without even the implied warranty of
235 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
236 * GNU General Public License for more details.
241 CREATE TABLE actor.stat_cat_entry (
242 id SERIAL PRIMARY KEY,
243 stat_cat INT NOT NULL,
246 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
248 COMMENT ON TABLE actor.stat_cat_entry IS $$
250 * Copyright (C) 2005 Georgia Public Library Service
251 * Mike Rylander <mrylander@gmail.com>
253 * User Statistical Catagory Entries
255 * Local data collected about Users is placed into a Statistical
256 * Catagory. Each library can create entries into any of it's own
257 * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
262 * This program is free software; you can redistribute it and/or
263 * modify it under the terms of the GNU General Public License
264 * as published by the Free Software Foundation; either version 2
265 * of the License, or (at your option) any later version.
267 * This program is distributed in the hope that it will be useful,
268 * but WITHOUT ANY WARRANTY; without even the implied warranty of
269 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
270 * GNU General Public License for more details.
275 CREATE TABLE actor.stat_cat_entry_usr_map (
276 id BIGSERIAL PRIMARY KEY,
277 stat_cat_entry TEXT NOT NULL,
278 stat_cat INT NOT NULL,
279 target_usr INT NOT NULL,
280 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
282 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
284 * Copyright (C) 2005 Georgia Public Library Service
285 * Mike Rylander <mrylander@gmail.com>
287 * Statistical Catagory Entry to User map
289 * Records the stat_cat entries for each user.
294 * This program is free software; you can redistribute it and/or
295 * modify it under the terms of the GNU General Public License
296 * as published by the Free Software Foundation; either version 2
297 * of the License, or (at your option) any later version.
299 * This program is distributed in the hope that it will be useful,
300 * but WITHOUT ANY WARRANTY; without even the implied warranty of
301 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
302 * GNU General Public License for more details.
306 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
308 CREATE TABLE actor.card (
309 id SERIAL PRIMARY KEY,
310 usr INT NOT NULL REFERENCES actor.usr (id),
311 barcode TEXT NOT NULL UNIQUE,
312 active BOOL NOT NULL DEFAULT TRUE
314 COMMENT ON TABLE actor.card IS $$
316 * Copyright (C) 2005 Georgia Public Library Service
317 * Mike Rylander <mrylander@gmail.com>
321 * Each User has one or more library cards. The current "main"
322 * card is linked to here from the actor.usr table, and it is up
323 * to the consortium policy whether more than one card can be
324 * active for any one user at a given time.
329 * This program is free software; you can redistribute it and/or
330 * modify it under the terms of the GNU General Public License
331 * as published by the Free Software Foundation; either version 2
332 * of the License, or (at your option) any later version.
334 * This program is distributed in the hope that it will be useful,
335 * but WITHOUT ANY WARRANTY; without even the implied warranty of
336 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
337 * GNU General Public License for more details.
341 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
343 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
346 CREATE TABLE actor.org_unit_type (
347 id SERIAL PRIMARY KEY,
349 opac_label TEXT NOT NULL,
351 parent INT REFERENCES actor.org_unit_type (id),
352 can_have_vols BOOL NOT NULL DEFAULT TRUE,
353 can_have_users BOOL NOT NULL DEFAULT TRUE
355 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
358 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE );
359 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 );
360 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
361 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
362 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
364 CREATE TABLE actor.org_unit (
365 id SERIAL PRIMARY KEY,
366 parent_ou INT REFERENCES actor.org_unit (id),
367 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id),
372 shortname TEXT NOT NULL,
375 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
376 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
377 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
378 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
379 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
380 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
382 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'CONS', 'Example Consortium');
383 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS1', 'Example System 1');
384 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS2', 'Example System 2');
385 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR1', 'Example Branch 1');
386 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2');
387 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3');
388 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4');
389 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL1', 'Example Sub-lib 1');
390 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM1', 'Example Bookmobile 1');
392 -- Workstation registration...
393 CREATE TABLE actor.workstation (
394 id SERIAL PRIMARY KEY,
395 name TEXT NOT NULL UNIQUE,
396 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
399 CREATE TABLE actor.org_unit_setting (
400 id BIGSERIAL PRIMARY KEY,
401 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
404 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
406 COMMENT ON TABLE actor.org_unit_setting IS $$
408 * Copyright (C) 2005 Georgia Public Library Service
409 * Mike Rylander <mrylander@gmail.com>
413 * This table contains any arbitrary settings that a client
414 * program would like to save for an org unit.
418 * This program is free software; you can redistribute it and/or
419 * modify it under the terms of the GNU General Public License
420 * as published by the Free Software Foundation; either version 2
421 * of the License, or (at your option) any later version.
423 * This program is distributed in the hope that it will be useful,
424 * but WITHOUT ANY WARRANTY; without even the implied warranty of
425 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
426 * GNU General Public License for more details.
430 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
433 CREATE TABLE actor.usr_address (
434 id SERIAL PRIMARY KEY,
435 valid BOOL NOT NULL DEFAULT TRUE,
436 within_city_limits BOOL NOT NULL DEFAULT TRUE,
437 address_type TEXT NOT NULL DEFAULT 'MAILING',
438 usr INT NOT NULL REFERENCES actor.usr (id),
439 street1 TEXT NOT NULL,
444 country TEXT NOT NULL,
445 post_code TEXT NOT NULL
448 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
450 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
451 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
453 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
454 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
455 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
458 CREATE TABLE actor.org_address (
459 id SERIAL PRIMARY KEY,
460 valid BOOL NOT NULL DEFAULT TRUE,
461 address_type TEXT NOT NULL DEFAULT 'MAILING',
462 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
463 street1 TEXT NOT NULL,
468 country TEXT NOT NULL,
469 post_code TEXT NOT NULL
472 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
474 INSERT INTO actor.org_address VALUES (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303');
475 UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1;