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 usrgroup SERIAL NOT NULL,
58 claims_returned_count INT NOT NULL DEFAULT 0,
59 credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00,
60 last_xact_id TEXT NOT NULL DEFAULT 'none',
62 create_date DATE NOT NULL DEFAULT now()::DATE,
63 expire_date DATE NOT NULL DEFAULT (now() + '3 years'::INTERVAL)::DATE
65 COMMENT ON TABLE actor.usr IS $$
67 * Copyright (C) 2005 Georgia Public Library Service
68 * Mike Rylander <mrylander@gmail.com>
72 * This table contains the core User objects that describe both
73 * staff members and patrons. The difference between the two
74 * types of users is based on the user's permissions.
78 * This program is free software; you can redistribute it and/or
79 * modify it under the terms of the GNU General Public License
80 * as published by the Free Software Foundation; either version 2
81 * of the License, or (at your option) any later version.
83 * This program is distributed in the hope that it will be useful,
84 * but WITHOUT ANY WARRANTY; without even the implied warranty of
85 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
86 * GNU General Public License for more details.
90 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
91 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
92 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
94 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name));
95 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name));
96 CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name));
98 CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email));
100 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone));
101 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone));
102 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone));
104 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value));
105 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2));
107 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
109 NEW.passwd = MD5( NEW.passwd );
114 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
116 IF NEW.passwd <> OLD.passwd THEN
117 NEW.passwd = MD5( NEW.passwd );
123 CREATE TRIGGER actor_crypt_pw_update_trigger
124 BEFORE UPDATE ON actor.usr FOR EACH ROW
125 EXECUTE PROCEDURE actor.crypt_pw_update ();
127 CREATE TRIGGER actor_crypt_pw_insert_trigger
128 BEFORE INSERT ON actor.usr FOR EACH ROW
129 EXECUTE PROCEDURE actor.crypt_pw_insert ();
131 -- Just so that there is a user...
132 INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value, home_ou )
133 VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', 'System Account', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 );
136 CREATE TABLE actor.usr_standing_penalty (
137 id SERIAL PRIMARY KEY,
138 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
139 penalty_type TEXT NOT NULL
141 COMMENT ON TABLE actor.usr_standing_penalty IS $$
143 * Copyright (C) 2005 Georgia Public Library Service
144 * Mike Rylander <mrylander@gmail.com>
146 * User standing penalties
150 * This program is free software; you can redistribute it and/or
151 * modify it under the terms of the GNU General Public License
152 * as published by the Free Software Foundation; either version 2
153 * of the License, or (at your option) any later version.
155 * This program is distributed in the hope that it will be useful,
156 * but WITHOUT ANY WARRANTY; without even the implied warranty of
157 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
158 * GNU General Public License for more details.
162 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
164 CREATE TABLE actor.usr_setting (
165 id BIGSERIAL PRIMARY KEY,
166 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
169 CONSTRAINT usr_once_per_key UNIQUE (usr,name)
171 COMMENT ON TABLE actor.usr_setting IS $$
173 * Copyright (C) 2005 Georgia Public Library Service
174 * Mike Rylander <mrylander@gmail.com>
178 * This table contains any arbitrary settings that a client
179 * program would like to save for a user.
183 * This program is free software; you can redistribute it and/or
184 * modify it under the terms of the GNU General Public License
185 * as published by the Free Software Foundation; either version 2
186 * of the License, or (at your option) any later version.
188 * This program is distributed in the hope that it will be useful,
189 * but WITHOUT ANY WARRANTY; without even the implied warranty of
190 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
191 * GNU General Public License for more details.
195 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
198 CREATE TABLE actor.stat_cat (
199 id SERIAL PRIMARY KEY,
202 opac_visible BOOL NOT NULL DEFAULT FALSE,
203 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
205 COMMENT ON TABLE actor.stat_cat IS $$
207 * Copyright (C) 2005 Georgia Public Library Service
208 * Mike Rylander <mrylander@gmail.com>
210 * User Statistical Catagories
212 * Local data collected about Users is placed into a Statistical
213 * Catagory. Here's where those catagories are defined.
217 * This program is free software; you can redistribute it and/or
218 * modify it under the terms of the GNU General Public License
219 * as published by the Free Software Foundation; either version 2
220 * of the License, or (at your option) any later version.
222 * This program is distributed in the hope that it will be useful,
223 * but WITHOUT ANY WARRANTY; without even the implied warranty of
224 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
225 * GNU General Public License for more details.
230 CREATE TABLE actor.stat_cat_entry (
231 id SERIAL PRIMARY KEY,
232 stat_cat INT NOT NULL,
235 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
237 COMMENT ON TABLE actor.stat_cat_entry IS $$
239 * Copyright (C) 2005 Georgia Public Library Service
240 * Mike Rylander <mrylander@gmail.com>
242 * User Statistical Catagory Entries
244 * Local data collected about Users is placed into a Statistical
245 * Catagory. Each library can create entries into any of it's own
246 * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
251 * This program is free software; you can redistribute it and/or
252 * modify it under the terms of the GNU General Public License
253 * as published by the Free Software Foundation; either version 2
254 * of the License, or (at your option) any later version.
256 * This program is distributed in the hope that it will be useful,
257 * but WITHOUT ANY WARRANTY; without even the implied warranty of
258 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
259 * GNU General Public License for more details.
264 CREATE TABLE actor.stat_cat_entry_usr_map (
265 id BIGSERIAL PRIMARY KEY,
266 stat_cat_entry TEXT NOT NULL,
267 stat_cat INT NOT NULL,
268 target_usr INT NOT NULL,
269 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
271 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
273 * Copyright (C) 2005 Georgia Public Library Service
274 * Mike Rylander <mrylander@gmail.com>
276 * Statistical Catagory Entry to User map
278 * Records the stat_cat entries for each user.
283 * This program is free software; you can redistribute it and/or
284 * modify it under the terms of the GNU General Public License
285 * as published by the Free Software Foundation; either version 2
286 * of the License, or (at your option) any later version.
288 * This program is distributed in the hope that it will be useful,
289 * but WITHOUT ANY WARRANTY; without even the implied warranty of
290 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
291 * GNU General Public License for more details.
295 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
297 CREATE TABLE actor.card (
298 id SERIAL PRIMARY KEY,
299 usr INT NOT NULL REFERENCES actor.usr (id),
300 barcode TEXT NOT NULL UNIQUE,
301 active BOOL NOT NULL DEFAULT TRUE
303 COMMENT ON TABLE actor.card IS $$
305 * Copyright (C) 2005 Georgia Public Library Service
306 * Mike Rylander <mrylander@gmail.com>
310 * Each User has one or more library cards. The current "main"
311 * card is linked to here from the actor.usr table, and it is up
312 * to the consortium policy whether more than one card can be
313 * active for any one user at a given time.
318 * This program is free software; you can redistribute it and/or
319 * modify it under the terms of the GNU General Public License
320 * as published by the Free Software Foundation; either version 2
321 * of the License, or (at your option) any later version.
323 * This program is distributed in the hope that it will be useful,
324 * but WITHOUT ANY WARRANTY; without even the implied warranty of
325 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
326 * GNU General Public License for more details.
330 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
332 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
335 CREATE TABLE actor.org_unit_type (
336 id SERIAL PRIMARY KEY,
338 opac_label TEXT NOT NULL,
340 parent INT REFERENCES actor.org_unit_type (id),
341 can_have_vols BOOL NOT NULL DEFAULT TRUE,
342 can_have_users BOOL NOT NULL DEFAULT TRUE
344 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
347 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE );
348 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 );
349 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
350 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
351 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
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,
364 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
365 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
366 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
367 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
368 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
369 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
371 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'CONS', 'Example Consortium');
372 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS1', 'Example System 1');
373 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS2', 'Example System 2');
374 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR1', 'Example Branch 1');
375 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2');
376 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3');
377 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4');
378 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL1', 'Example Sub-lib 1');
379 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM1', 'Example Bookmobile 1');
381 CREATE TABLE actor.org_unit_setting (
382 id BIGSERIAL PRIMARY KEY,
383 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
386 CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
388 COMMENT ON TABLE actor.org_unit_setting IS $$
390 * Copyright (C) 2005 Georgia Public Library Service
391 * Mike Rylander <mrylander@gmail.com>
395 * This table contains any arbitrary settings that a client
396 * program would like to save for an org unit.
400 * This program is free software; you can redistribute it and/or
401 * modify it under the terms of the GNU General Public License
402 * as published by the Free Software Foundation; either version 2
403 * of the License, or (at your option) any later version.
405 * This program is distributed in the hope that it will be useful,
406 * but WITHOUT ANY WARRANTY; without even the implied warranty of
407 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
408 * GNU General Public License for more details.
412 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
415 CREATE TABLE actor.usr_address (
416 id SERIAL PRIMARY KEY,
417 valid BOOL NOT NULL DEFAULT TRUE,
418 address_type TEXT NOT NULL DEFAULT 'MAILING',
419 usr INT NOT NULL REFERENCES actor.usr (id),
420 street1 TEXT NOT NULL,
425 country TEXT NOT NULL,
426 post_code TEXT NOT NULL
429 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
431 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
432 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
434 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
435 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
436 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
439 CREATE TABLE actor.org_address (
440 id SERIAL PRIMARY KEY,
441 valid BOOL NOT NULL DEFAULT TRUE,
442 address_type TEXT NOT NULL DEFAULT 'MAILING',
443 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
444 street1 TEXT NOT NULL,
449 country TEXT NOT NULL,
450 post_code TEXT NOT NULL
453 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
455 INSERT INTO actor.org_address VALUES (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303');
456 UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1;