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_setting (
137 id BIGSERIAL PRIMARY KEY,
138 usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
141 CONSTRAINT name_once_per_value UNIQUE (usr,name)
143 COMMENT ON TABLE actor.usr_setting IS $$
145 * Copyright (C) 2005 Georgia Public Library Service
146 * Mike Rylander <mrylander@gmail.com>
150 * This table contains any arbitrary settings that a client
151 * program would like to save for a user.
155 * This program is free software; you can redistribute it and/or
156 * modify it under the terms of the GNU General Public License
157 * as published by the Free Software Foundation; either version 2
158 * of the License, or (at your option) any later version.
160 * This program is distributed in the hope that it will be useful,
161 * but WITHOUT ANY WARRANTY; without even the implied warranty of
162 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
163 * GNU General Public License for more details.
167 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
170 CREATE TABLE actor.stat_cat (
171 id SERIAL PRIMARY KEY,
174 opac_visible BOOL NOT NULL DEFAULT FALSE,
175 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
177 COMMENT ON TABLE actor.stat_cat IS $$
179 * Copyright (C) 2005 Georgia Public Library Service
180 * Mike Rylander <mrylander@gmail.com>
182 * User Statistical Catagories
184 * Local data collected about Users is placed into a Statistical
185 * Catagory. Here's where those catagories are defined.
189 * This program is free software; you can redistribute it and/or
190 * modify it under the terms of the GNU General Public License
191 * as published by the Free Software Foundation; either version 2
192 * of the License, or (at your option) any later version.
194 * This program is distributed in the hope that it will be useful,
195 * but WITHOUT ANY WARRANTY; without even the implied warranty of
196 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
197 * GNU General Public License for more details.
202 CREATE TABLE actor.stat_cat_entry (
203 id SERIAL PRIMARY KEY,
204 stat_cat INT NOT NULL,
207 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
209 COMMENT ON TABLE actor.stat_cat_entry IS $$
211 * Copyright (C) 2005 Georgia Public Library Service
212 * Mike Rylander <mrylander@gmail.com>
214 * User Statistical Catagory Entries
216 * Local data collected about Users is placed into a Statistical
217 * Catagory. Each library can create entries into any of it's own
218 * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
223 * This program is free software; you can redistribute it and/or
224 * modify it under the terms of the GNU General Public License
225 * as published by the Free Software Foundation; either version 2
226 * of the License, or (at your option) any later version.
228 * This program is distributed in the hope that it will be useful,
229 * but WITHOUT ANY WARRANTY; without even the implied warranty of
230 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
231 * GNU General Public License for more details.
236 CREATE TABLE actor.stat_cat_entry_usr_map (
237 id BIGSERIAL PRIMARY KEY,
238 stat_cat_entry TEXT NOT NULL,
239 stat_cat INT NOT NULL,
240 target_usr INT NOT NULL,
241 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
243 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
245 * Copyright (C) 2005 Georgia Public Library Service
246 * Mike Rylander <mrylander@gmail.com>
248 * Statistical Catagory Entry to User map
250 * Records the stat_cat entries for each user.
255 * This program is free software; you can redistribute it and/or
256 * modify it under the terms of the GNU General Public License
257 * as published by the Free Software Foundation; either version 2
258 * of the License, or (at your option) any later version.
260 * This program is distributed in the hope that it will be useful,
261 * but WITHOUT ANY WARRANTY; without even the implied warranty of
262 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
263 * GNU General Public License for more details.
267 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
269 CREATE TABLE actor.card (
270 id SERIAL PRIMARY KEY,
271 usr INT NOT NULL REFERENCES actor.usr (id),
272 barcode TEXT NOT NULL UNIQUE,
273 active BOOL NOT NULL DEFAULT TRUE
275 COMMENT ON TABLE actor.card IS $$
277 * Copyright (C) 2005 Georgia Public Library Service
278 * Mike Rylander <mrylander@gmail.com>
282 * Each User has one or more library cards. The current "main"
283 * card is linked to here from the actor.usr table, and it is up
284 * to the consortium policy whether more than one card can be
285 * active for any one user at a given time.
290 * This program is free software; you can redistribute it and/or
291 * modify it under the terms of the GNU General Public License
292 * as published by the Free Software Foundation; either version 2
293 * of the License, or (at your option) any later version.
295 * This program is distributed in the hope that it will be useful,
296 * but WITHOUT ANY WARRANTY; without even the implied warranty of
297 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
298 * GNU General Public License for more details.
302 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
304 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
307 CREATE TABLE actor.org_unit_type (
308 id SERIAL PRIMARY KEY,
310 opac_label TEXT NOT NULL,
312 parent INT REFERENCES actor.org_unit_type (id),
313 can_have_vols BOOL NOT NULL DEFAULT TRUE,
314 can_have_users BOOL NOT NULL DEFAULT TRUE
316 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
319 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE );
320 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 );
321 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
322 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
323 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
325 CREATE TABLE actor.org_unit (
326 id SERIAL PRIMARY KEY,
327 parent_ou INT REFERENCES actor.org_unit (id),
328 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id),
333 shortname TEXT NOT NULL,
336 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
337 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
338 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
339 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
340 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
341 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
343 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'CONS', 'Example Consortium');
344 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS1', 'Example System 1');
345 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS2', 'Example System 2');
346 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR1', 'Example Branch 1');
347 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2');
348 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3');
349 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4');
350 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL1', 'Example Sub-lib 1');
351 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM1', 'Example Bookmobile 1');
353 CREATE TABLE actor.org_unit_setting (
354 id BIGSERIAL PRIMARY KEY,
355 org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
358 CONSTRAINT name_once_per_value UNIQUE (org_unit,name)
360 COMMENT ON TABLE actor.org_unit_setting IS $$
362 * Copyright (C) 2005 Georgia Public Library Service
363 * Mike Rylander <mrylander@gmail.com>
367 * This table contains any arbitrary settings that a client
368 * program would like to save for an org unit.
372 * This program is free software; you can redistribute it and/or
373 * modify it under the terms of the GNU General Public License
374 * as published by the Free Software Foundation; either version 2
375 * of the License, or (at your option) any later version.
377 * This program is distributed in the hope that it will be useful,
378 * but WITHOUT ANY WARRANTY; without even the implied warranty of
379 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
380 * GNU General Public License for more details.
384 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
387 CREATE TABLE actor.usr_address (
388 id SERIAL PRIMARY KEY,
389 valid BOOL NOT NULL DEFAULT TRUE,
390 address_type TEXT NOT NULL DEFAULT 'MAILING',
391 usr INT NOT NULL REFERENCES actor.usr (id),
392 street1 TEXT NOT NULL,
397 country TEXT NOT NULL,
398 post_code TEXT NOT NULL
401 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
403 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
404 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
406 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
407 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
408 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
411 CREATE TABLE actor.org_address (
412 id SERIAL PRIMARY KEY,
413 valid BOOL NOT NULL DEFAULT TRUE,
414 address_type TEXT NOT NULL DEFAULT 'MAILING',
415 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
416 street1 TEXT NOT NULL,
421 country TEXT NOT NULL,
422 post_code TEXT NOT NULL
425 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
427 INSERT INTO actor.org_address VALUES (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303');
428 UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1;