moving sql files to dir matching the Open-ILS driver name (sorry for killing the...
[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', '', '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 objects
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 TABLE actor.stat_cat (
168         id              SERIAL  PRIMARY KEY,
169         owner           INT     NOT NULL,
170         name            TEXT    NOT NULL,
171         opac_visible    BOOL NOT NULL DEFAULT FALSE,
172         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
173 );
174 COMMENT ON TABLE actor.stat_cat IS $$
175 /*
176  * Copyright (C) 2005  Georgia Public Library Service 
177  * Mike Rylander <mrylander@gmail.com>
178  *
179  * User Statistical Catagories
180  *
181  * Local data collected about Users is placed into a Statistical
182  * Catagory.  Here's where those catagories are defined.
183  *
184  * ****
185  *
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.
190  *
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.
195  */
196 $$;
197
198
199 CREATE TABLE actor.stat_cat_entry (
200         id              SERIAL  PRIMARY KEY,
201         stat_cat        INT     NOT NULL,
202         owner           INT     NOT NULL,
203         value           TEXT    NOT NULL,
204         CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
205 );
206 COMMENT ON TABLE actor.stat_cat_entry IS $$
207 /*
208  * Copyright (C) 2005  Georgia Public Library Service 
209  * Mike Rylander <mrylander@gmail.com>
210  *
211  * User Statistical Catagory Entries
212  *
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.
216  *
217  *
218  * ****
219  *
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.
224  *
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.
229  */
230 $$;
231
232
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)
239 );
240 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
241 /*
242  * Copyright (C) 2005  Georgia Public Library Service 
243  * Mike Rylander <mrylander@gmail.com>
244  *
245  * Statistical Catagory Entry to User map
246  *
247  * Records the stat_cat entries for each user.
248  *
249  *
250  * ****
251  *
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.
256  *
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.
261  */
262 $$;
263
264 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
265
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
271 );
272 COMMENT ON TABLE actor.card IS $$
273 /*
274  * Copyright (C) 2005  Georgia Public Library Service 
275  * Mike Rylander <mrylander@gmail.com>
276  *
277  * Library Cards
278  *
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.
283  *
284  *
285  * ****
286  *
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.
291  *
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.
296  */
297 $$;
298
299 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
300
301 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
302
303
304 CREATE TABLE actor.org_unit_type (
305         id              SERIAL  PRIMARY KEY,
306         name            TEXT    NOT NULL,
307         opac_label      TEXT    NOT NULL,
308         depth           INT     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
312 );
313 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
314
315 -- The PINES levels
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 );
321
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),
326         ill_address     INT,
327         holds_address   INT,
328         mailing_address INT,
329         billing_address INT,
330         shortname       TEXT    NOT NULL,
331         name            TEXT    NOT NULL
332 );
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);
339
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');
349
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,
356         street2         TEXT,
357         city            TEXT    NOT NULL,
358         county          TEXT,
359         state           TEXT    NOT NULL,
360         country         TEXT    NOT NULL,
361         post_code       TEXT    NOT NULL
362 );
363
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));
366
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));
370
371
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,
378         street2         TEXT,
379         city            TEXT    NOT NULL,
380         county          TEXT,
381         state           TEXT    NOT NULL,
382         country         TEXT    NOT NULL,
383         post_code       TEXT    NOT NULL
384 );
385
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;
388
389 COMMIT;