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', '', '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 TABLE actor.stat_cat (
168 id SERIAL PRIMARY KEY,
171 opac_visible BOOL NOT NULL DEFAULT FALSE,
172 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
174 COMMENT ON TABLE actor.stat_cat IS $$
176 * Copyright (C) 2005 Georgia Public Library Service
177 * Mike Rylander <mrylander@gmail.com>
179 * User Statistical Catagories
181 * Local data collected about Users is placed into a Statistical
182 * Catagory. Here's where those catagories are defined.
186 * This program is free software; you can redistribute it and/or
187 * modify it under the terms of the GNU General Public License
188 * as published by the Free Software Foundation; either version 2
189 * of the License, or (at your option) any later version.
191 * This program is distributed in the hope that it will be useful,
192 * but WITHOUT ANY WARRANTY; without even the implied warranty of
193 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
194 * GNU General Public License for more details.
199 CREATE TABLE actor.stat_cat_entry (
200 id SERIAL PRIMARY KEY,
201 stat_cat INT NOT NULL,
204 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
206 COMMENT ON TABLE actor.stat_cat_entry IS $$
208 * Copyright (C) 2005 Georgia Public Library Service
209 * Mike Rylander <mrylander@gmail.com>
211 * User Statistical Catagory Entries
213 * Local data collected about Users is placed into a Statistical
214 * Catagory. Each library can create entries into any of it's own
215 * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
220 * This program is free software; you can redistribute it and/or
221 * modify it under the terms of the GNU General Public License
222 * as published by the Free Software Foundation; either version 2
223 * of the License, or (at your option) any later version.
225 * This program is distributed in the hope that it will be useful,
226 * but WITHOUT ANY WARRANTY; without even the implied warranty of
227 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
228 * GNU General Public License for more details.
233 CREATE TABLE actor.stat_cat_entry_usr_map (
234 id BIGSERIAL PRIMARY KEY,
235 stat_cat_entry TEXT NOT NULL,
236 stat_cat INT NOT NULL,
237 target_usr INT NOT NULL,
238 CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
240 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
242 * Copyright (C) 2005 Georgia Public Library Service
243 * Mike Rylander <mrylander@gmail.com>
245 * Statistical Catagory Entry to User map
247 * Records the stat_cat entries for each user.
252 * This program is free software; you can redistribute it and/or
253 * modify it under the terms of the GNU General Public License
254 * as published by the Free Software Foundation; either version 2
255 * of the License, or (at your option) any later version.
257 * This program is distributed in the hope that it will be useful,
258 * but WITHOUT ANY WARRANTY; without even the implied warranty of
259 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
260 * GNU General Public License for more details.
264 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
266 CREATE TABLE actor.card (
267 id SERIAL PRIMARY KEY,
268 usr INT NOT NULL REFERENCES actor.usr (id),
269 barcode TEXT NOT NULL UNIQUE,
270 active BOOL NOT NULL DEFAULT TRUE
272 COMMENT ON TABLE actor.card IS $$
274 * Copyright (C) 2005 Georgia Public Library Service
275 * Mike Rylander <mrylander@gmail.com>
279 * Each User has one or more library cards. The current "main"
280 * card is linked to here from the actor.usr table, and it is up
281 * to the consortium policy whether more than one card can be
282 * active for any one user at a given time.
287 * This program is free software; you can redistribute it and/or
288 * modify it under the terms of the GNU General Public License
289 * as published by the Free Software Foundation; either version 2
290 * of the License, or (at your option) any later version.
292 * This program is distributed in the hope that it will be useful,
293 * but WITHOUT ANY WARRANTY; without even the implied warranty of
294 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
295 * GNU General Public License for more details.
299 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
301 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
304 CREATE TABLE actor.org_unit_type (
305 id SERIAL PRIMARY KEY,
307 opac_label TEXT NOT NULL,
309 parent INT REFERENCES actor.org_unit_type (id),
310 can_have_vols BOOL NOT NULL DEFAULT TRUE,
311 can_have_users BOOL NOT NULL DEFAULT TRUE
313 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
316 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE );
317 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 );
318 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
319 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
320 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
322 CREATE TABLE actor.org_unit (
323 id SERIAL PRIMARY KEY,
324 parent_ou INT REFERENCES actor.org_unit (id),
325 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id),
330 shortname TEXT NOT NULL,
333 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
334 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
335 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
336 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
337 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
338 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
340 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'CONS', 'Example Consortium');
341 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS1', 'Example System 1');
342 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS2', 'Example System 2');
343 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR1', 'Example Branch 1');
344 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2');
345 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3');
346 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4');
347 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL4', 'Example Sub-lib 1');
348 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM4', 'Example Bookmobile 1');
350 CREATE TABLE actor.usr_address (
351 id SERIAL PRIMARY KEY,
352 valid BOOL NOT NULL DEFAULT TRUE,
353 address_type TEXT NOT NULL DEFAULT 'MAILING',
354 usr INT NOT NULL REFERENCES actor.usr (id),
355 street1 TEXT NOT NULL,
360 country TEXT NOT NULL,
361 post_code TEXT NOT NULL
364 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
365 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
367 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
368 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
369 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
372 CREATE TABLE actor.org_address (
373 id SERIAL PRIMARY KEY,
374 valid BOOL NOT NULL DEFAULT TRUE,
375 address_type TEXT NOT NULL DEFAULT 'MAILING',
376 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
377 street1 TEXT NOT NULL,
382 country TEXT NOT NULL,
383 post_code TEXT NOT NULL
386 INSERT INTO actor.org_address (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303');
387 UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1;