schema tweaks
[Evergreen.git] / Open-ILS / src / sql / Pg / 005.schema.actors.sql
1 DROP SCHEMA actor CASCADE;
2
3 BEGIN;
4 CREATE SCHEMA actor;
5 COMMENT ON SCHEMA actor IS $$
6 /*
7  * Copyright (C) 2005  Georgia Public Library Service 
8  * Mike Rylander <mrylander@gmail.com>
9  *
10  * Schema: actor
11  *
12  * Holds all tables pertaining to users and libraries (org units).
13  *
14  * ****
15  *
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.
20  *
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.
25  */
26 $$;
27
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,
33         email                   TEXT,
34         passwd                  TEXT            NOT NULL,
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),
39         ident_value2            TEXT,
40         net_access_level        INT             NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id),
41         photo_url               TEXT,
42         prefix                  TEXT,
43         first_given_name        TEXT            NOT NULL,
44         second_given_name       TEXT,
45         family_name             TEXT            NOT NULL,
46         suffix                  TEXT,
47         day_phone               TEXT,
48         evening_phone           TEXT,
49         other_phone             TEXT,
50         mailing_address         INT,
51         billing_address         INT,
52         home_ou                 INT             NOT NULL,
53         dob                     DATE            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',
61         alert_message           TEXT,
62         create_date             DATE            NOT NULL DEFAULT now()::DATE,
63         expire_date             DATE            NOT NULL DEFAULT (now() + '3 years'::INTERVAL)::DATE
64 );
65 COMMENT ON TABLE actor.usr IS $$
66 /*
67  * Copyright (C) 2005  Georgia Public Library Service 
68  * Mike Rylander <mrylander@gmail.com>
69  *
70  * User objects
71  *
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.
75  *
76  * ****
77  *
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.
82  *
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.
87  */
88 $$;
89
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);
93
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));
97
98 CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email));
99
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));
103
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));
106
107 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
108         BEGIN
109                 NEW.passwd = MD5( NEW.passwd );
110                 RETURN NEW;
111         END;
112 $$ LANGUAGE PLPGSQL;
113
114 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
115         BEGIN
116                 IF NEW.passwd <> OLD.passwd THEN
117                         NEW.passwd = MD5( NEW.passwd );
118                 END IF;
119                 RETURN NEW;
120         END;
121 $$ LANGUAGE PLPGSQL;
122
123 CREATE TRIGGER actor_crypt_pw_update_trigger
124         BEFORE UPDATE ON actor.usr FOR EACH ROW
125         EXECUTE PROCEDURE actor.crypt_pw_update ();
126
127 CREATE TRIGGER actor_crypt_pw_insert_trigger
128         BEFORE INSERT ON actor.usr FOR EACH ROW
129         EXECUTE PROCEDURE actor.crypt_pw_insert ();
130
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 );
134
135
136 CREATE TABLE actor.usr_setting (
137         id      BIGSERIAL       PRIMARY KEY,
138         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
139         name    TEXT            NOT NULL,
140         value   TEXT            NOT NULL,
141         CONSTRAINT name_once_per_value UNIQUE (usr,name)
142 );
143 COMMENT ON TABLE actor.usr_setting IS $$
144 /*
145  * Copyright (C) 2005  Georgia Public Library Service 
146  * Mike Rylander <mrylander@gmail.com>
147  *
148  * User settings
149  *
150  * This table contains any arbitrary settings that a client
151  * program would like to save for a user.
152  *
153  * ****
154  *
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.
159  *
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.
164  */
165 $$;
166
167 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
168
169
170 CREATE TABLE actor.stat_cat (
171         id              SERIAL  PRIMARY KEY,
172         owner           INT     NOT NULL,
173         name            TEXT    NOT NULL,
174         opac_visible    BOOL NOT NULL DEFAULT FALSE,
175         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
176 );
177 COMMENT ON TABLE actor.stat_cat IS $$
178 /*
179  * Copyright (C) 2005  Georgia Public Library Service 
180  * Mike Rylander <mrylander@gmail.com>
181  *
182  * User Statistical Catagories
183  *
184  * Local data collected about Users is placed into a Statistical
185  * Catagory.  Here's where those catagories are defined.
186  *
187  * ****
188  *
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.
193  *
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.
198  */
199 $$;
200
201
202 CREATE TABLE actor.stat_cat_entry (
203         id              SERIAL  PRIMARY KEY,
204         stat_cat        INT     NOT NULL,
205         owner           INT     NOT NULL,
206         value           TEXT    NOT NULL,
207         CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
208 );
209 COMMENT ON TABLE actor.stat_cat_entry IS $$
210 /*
211  * Copyright (C) 2005  Georgia Public Library Service 
212  * Mike Rylander <mrylander@gmail.com>
213  *
214  * User Statistical Catagory Entries
215  *
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.
219  *
220  *
221  * ****
222  *
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.
227  *
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.
232  */
233 $$;
234
235
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)
242 );
243 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
244 /*
245  * Copyright (C) 2005  Georgia Public Library Service 
246  * Mike Rylander <mrylander@gmail.com>
247  *
248  * Statistical Catagory Entry to User map
249  *
250  * Records the stat_cat entries for each user.
251  *
252  *
253  * ****
254  *
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.
259  *
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.
264  */
265 $$;
266
267 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
268
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
274 );
275 COMMENT ON TABLE actor.card IS $$
276 /*
277  * Copyright (C) 2005  Georgia Public Library Service 
278  * Mike Rylander <mrylander@gmail.com>
279  *
280  * Library Cards
281  *
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.
286  *
287  *
288  * ****
289  *
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.
294  *
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.
299  */
300 $$;
301
302 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
303
304 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
305
306
307 CREATE TABLE actor.org_unit_type (
308         id              SERIAL  PRIMARY KEY,
309         name            TEXT    NOT NULL,
310         opac_label      TEXT    NOT NULL,
311         depth           INT     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
315 );
316 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
317
318 -- The PINES levels
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 );
324
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),
329         ill_address     INT,
330         holds_address   INT,
331         mailing_address INT,
332         billing_address INT,
333         shortname       TEXT    NOT NULL,
334         name            TEXT    NOT NULL
335 );
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);
342
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');
352
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,
356         name            TEXT            NOT NULL,
357         value           TEXT            NOT NULL,
358         CONSTRAINT name_once_per_value UNIQUE (org_unit,name)
359 );
360 COMMENT ON TABLE actor.org_unit_setting IS $$
361 /*
362  * Copyright (C) 2005  Georgia Public Library Service 
363  * Mike Rylander <mrylander@gmail.com>
364  *
365  * Org Unit settings
366  *
367  * This table contains any arbitrary settings that a client
368  * program would like to save for an org unit.
369  *
370  * ****
371  *
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.
376  *
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.
381  */
382 $$;
383
384 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
385
386
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,
393         street2         TEXT,
394         city            TEXT    NOT NULL,
395         county          TEXT,
396         state           TEXT    NOT NULL,
397         country         TEXT    NOT NULL,
398         post_code       TEXT    NOT NULL
399 );
400
401 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
402
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));
405
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));
409
410
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,
417         street2         TEXT,
418         city            TEXT    NOT NULL,
419         county          TEXT,
420         state           TEXT    NOT NULL,
421         country         TEXT    NOT NULL,
422         post_code       TEXT    NOT NULL
423 );
424
425 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
426
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;
429
430 COMMIT;