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         barred                  BOOL            NOT NULL DEFAULT FALSE,
  58         usrgroup                SERIAL          NOT NULL,
  59         claims_returned_count   INT             NOT NULL DEFAULT 0,
  60         credit_forward_balance  NUMERIC(6,2)    NOT NULL DEFAULT 0.00,
  61         last_xact_id            TEXT            NOT NULL DEFAULT 'none',
  62         alert_message           TEXT,
  63         create_date             DATE            NOT NULL DEFAULT now()::DATE,
  64         expire_date             DATE            NOT NULL DEFAULT (now() + '3 years'::INTERVAL)::DATE
  65 );
  66 COMMENT ON TABLE actor.usr IS $$
  67 /*
  68  * Copyright (C) 2005  Georgia Public Library Service
  69  * Mike Rylander <mrylander@gmail.com>
  70  *
  71  * User objects
  72  *
  73  * This table contains the core User objects that describe both
  74  * staff members and patrons.  The difference between the two
  75  * types of users is based on the user's permissions.
  76  *
  77  * ****
  78  *
  79  * This program is free software; you can redistribute it and/or
  80  * modify it under the terms of the GNU General Public License
  81  * as published by the Free Software Foundation; either version 2
  82  * of the License, or (at your option) any later version.
  83  *
  84  * This program is distributed in the hope that it will be useful,
  85  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  86  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  87  * GNU General Public License for more details.
  88  */
  89 $$;
  90
  91 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
  92 CREATE INDEX actor_usr_mailing_address_idx ON actor.usr (mailing_address);
  93 CREATE INDEX actor_usr_billing_address_idx ON actor.usr (billing_address);
  94
  95 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (lower(first_given_name));
  96 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (lower(second_given_name));
  97 CREATE INDEX actor_usr_family_name_idx ON actor.usr (lower(family_name));
  98
  99 CREATE INDEX actor_usr_email_idx ON actor.usr (lower(email));
 100
 101 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (lower(day_phone));
 102 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (lower(evening_phone));
 103 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (lower(other_phone));
 104
 105 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (lower(ident_value));
 106 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (lower(ident_value2));
 107
 108 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
 109         BEGIN
 110                 NEW.passwd = MD5( NEW.passwd );
 111                 RETURN NEW;
 112         END;
 113 $$ LANGUAGE PLPGSQL;
 114
 115 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
 116         BEGIN
 117                 IF NEW.passwd <> OLD.passwd THEN
 118                         NEW.passwd = MD5( NEW.passwd );
 119                 END IF;
 120                 RETURN NEW;
 121         END;
 122 $$ LANGUAGE PLPGSQL;
 123
 124 CREATE TRIGGER actor_crypt_pw_update_trigger
 125         BEFORE UPDATE ON actor.usr FOR EACH ROW
 126         EXECUTE PROCEDURE actor.crypt_pw_update ();
 127
 128 CREATE TRIGGER actor_crypt_pw_insert_trigger
 129         BEFORE INSERT ON actor.usr FOR EACH ROW
 130         EXECUTE PROCEDURE actor.crypt_pw_insert ();
 131
 132 -- Just so that there is a user...
 133 INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value, home_ou )
 134         VALUES ( 1, 1,'admin', 'open-ils', 'Administrator', 'System Account', '1979-01-22', TRUE, TRUE, 1, 'identification', 1 );
 135
 136
 137 CREATE TABLE actor.usr_standing_penalty (
 138         id              SERIAL  PRIMARY KEY,
 139         usr             INT     NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
 140         penalty_type    TEXT    NOT NULL
 141 );
 142 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 143 /*
 144  * Copyright (C) 2005  Georgia Public Library Service
 145  * Mike Rylander <mrylander@gmail.com>
 146  *
 147  * User standing penalties
 148  *
 149  * ****
 150  *
 151  * This program is free software; you can redistribute it and/or
 152  * modify it under the terms of the GNU General Public License
 153  * as published by the Free Software Foundation; either version 2
 154  * of the License, or (at your option) any later version.
 155  *
 156  * This program is distributed in the hope that it will be useful,
 157  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 158  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 159  * GNU General Public License for more details.
 160  */
 161 $$;
 162
 163 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 164
 165 CREATE TABLE actor.usr_setting (
 166         id      BIGSERIAL       PRIMARY KEY,
 167         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
 168         name    TEXT            NOT NULL,
 169         value   TEXT            NOT NULL,
 170         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
 171 );
 172 COMMENT ON TABLE actor.usr_setting IS $$
 173 /*
 174  * Copyright (C) 2005  Georgia Public Library Service
 175  * Mike Rylander <mrylander@gmail.com>
 176  *
 177  * User settings
 178  *
 179  * This table contains any arbitrary settings that a client
 180  * program would like to save for a user.
 181  *
 182  * ****
 183  *
 184  * This program is free software; you can redistribute it and/or
 185  * modify it under the terms of the GNU General Public License
 186  * as published by the Free Software Foundation; either version 2
 187  * of the License, or (at your option) any later version.
 188  *
 189  * This program is distributed in the hope that it will be useful,
 190  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 191  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 192  * GNU General Public License for more details.
 193  */
 194 $$;
 195
 196 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
 197
 198
 199 CREATE TABLE actor.stat_cat (
 200         id              SERIAL  PRIMARY KEY,
 201         owner           INT     NOT NULL,
 202         name            TEXT    NOT NULL,
 203         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 204         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 205 );
 206 COMMENT ON TABLE actor.stat_cat IS $$
 207 /*
 208  * Copyright (C) 2005  Georgia Public Library Service
 209  * Mike Rylander <mrylander@gmail.com>
 210  *
 211  * User Statistical Catagories
 212  *
 213  * Local data collected about Users is placed into a Statistical
 214  * Catagory.  Here's where those catagories are defined.
 215  *
 216  * ****
 217  *
 218  * This program is free software; you can redistribute it and/or
 219  * modify it under the terms of the GNU General Public License
 220  * as published by the Free Software Foundation; either version 2
 221  * of the License, or (at your option) any later version.
 222  *
 223  * This program is distributed in the hope that it will be useful,
 224  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 225  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 226  * GNU General Public License for more details.
 227  */
 228 $$;
 229
 230
 231 CREATE TABLE actor.stat_cat_entry (
 232         id              SERIAL  PRIMARY KEY,
 233         stat_cat        INT     NOT NULL,
 234         owner           INT     NOT NULL,
 235         value           TEXT    NOT NULL,
 236         CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
 237 );
 238 COMMENT ON TABLE actor.stat_cat_entry IS $$
 239 /*
 240  * Copyright (C) 2005  Georgia Public Library Service
 241  * Mike Rylander <mrylander@gmail.com>
 242  *
 243  * User Statistical Catagory Entries
 244  *
 245  * Local data collected about Users is placed into a Statistical
 246  * Catagory.  Each library can create entries into any of it's own
 247  * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
 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
 265 CREATE TABLE actor.stat_cat_entry_usr_map (
 266         id              BIGSERIAL       PRIMARY KEY,
 267         stat_cat_entry  TEXT            NOT NULL,
 268         stat_cat        INT             NOT NULL,
 269         target_usr      INT             NOT NULL,
 270         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
 271 );
 272 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 273 /*
 274  * Copyright (C) 2005  Georgia Public Library Service
 275  * Mike Rylander <mrylander@gmail.com>
 276  *
 277  * Statistical Catagory Entry to User map
 278  *
 279  * Records the stat_cat entries for each user.
 280  *
 281  *
 282  * ****
 283  *
 284  * This program is free software; you can redistribute it and/or
 285  * modify it under the terms of the GNU General Public License
 286  * as published by the Free Software Foundation; either version 2
 287  * of the License, or (at your option) any later version.
 288  *
 289  * This program is distributed in the hope that it will be useful,
 290  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 291  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 292  * GNU General Public License for more details.
 293  */
 294 $$;
 295
 296 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 297
 298 CREATE TABLE actor.card (
 299         id      SERIAL  PRIMARY KEY,
 300         usr     INT     NOT NULL REFERENCES actor.usr (id),
 301         barcode TEXT    NOT NULL UNIQUE,
 302         active  BOOL    NOT NULL DEFAULT TRUE
 303 );
 304 COMMENT ON TABLE actor.card IS $$
 305 /*
 306  * Copyright (C) 2005  Georgia Public Library Service
 307  * Mike Rylander <mrylander@gmail.com>
 308  *
 309  * Library Cards
 310  *
 311  * Each User has one or more library cards.  The current "main"
 312  * card is linked to here from the actor.usr table, and it is up
 313  * to the consortium policy whether more than one card can be
 314  * active for any one user at a given time.
 315  *
 316  *
 317  * ****
 318  *
 319  * This program is free software; you can redistribute it and/or
 320  * modify it under the terms of the GNU General Public License
 321  * as published by the Free Software Foundation; either version 2
 322  * of the License, or (at your option) any later version.
 323  *
 324  * This program is distributed in the hope that it will be useful,
 325  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 326  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 327  * GNU General Public License for more details.
 328  */
 329 $$;
 330
 331 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 332
 333 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
 334
 335
 336 CREATE TABLE actor.org_unit_type (
 337         id              SERIAL  PRIMARY KEY,
 338         name            TEXT    NOT NULL,
 339         opac_label      TEXT    NOT NULL,
 340         depth           INT     NOT NULL,
 341         parent          INT     REFERENCES actor.org_unit_type (id),
 342         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 343         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 344 );
 345 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 346
 347 -- The PINES levels
 348 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE );
 349 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 );
 350 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
 351 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
 352 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
 353
 354 CREATE TABLE actor.org_unit (
 355         id              SERIAL  PRIMARY KEY,
 356         parent_ou       INT     REFERENCES actor.org_unit (id),
 357         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id),
 358         ill_address     INT,
 359         holds_address   INT,
 360         mailing_address INT,
 361         billing_address INT,
 362         shortname       TEXT    NOT NULL,
 363         name            TEXT    NOT NULL
 364 );
 365 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 366 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 367 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 368 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 369 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 370 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 371
 372 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'CONS', 'Example Consortium');
 373 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS1', 'Example System 1');
 374 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS2', 'Example System 2');
 375 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR1', 'Example Branch 1');
 376 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2');
 377 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3');
 378 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4');
 379 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL1', 'Example Sub-lib 1');
 380 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM1', 'Example Bookmobile 1');
 381
 382 -- Workstation registration...
 383 CREATE TABLE actor.workstation (
 384         id              SERIAL  PRIMARY KEY,
 385         name            TEXT    NOT NULL UNIQUE,
 386         owning_lib      INT     NOT NULL REFERENCES actor.org_unit (id)
 387 );
 388
 389 CREATE TABLE actor.org_unit_setting (
 390         id              BIGSERIAL       PRIMARY KEY,
 391         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
 392         name            TEXT            NOT NULL,
 393         value           TEXT            NOT NULL,
 394         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
 395 );
 396 COMMENT ON TABLE actor.org_unit_setting IS $$
 397 /*
 398  * Copyright (C) 2005  Georgia Public Library Service
 399  * Mike Rylander <mrylander@gmail.com>
 400  *
 401  * Org Unit settings
 402  *
 403  * This table contains any arbitrary settings that a client
 404  * program would like to save for an org unit.
 405  *
 406  * ****
 407  *
 408  * This program is free software; you can redistribute it and/or
 409  * modify it under the terms of the GNU General Public License
 410  * as published by the Free Software Foundation; either version 2
 411  * of the License, or (at your option) any later version.
 412  *
 413  * This program is distributed in the hope that it will be useful,
 414  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 415  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 416  * GNU General Public License for more details.
 417  */
 418 $$;
 419
 420 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 421
 422
 423 CREATE TABLE actor.usr_address (
 424         id                      SERIAL  PRIMARY KEY,
 425         valid                   BOOL    NOT NULL DEFAULT TRUE,
 426         within_city_limits      BOOL    NOT NULL DEFAULT TRUE,
 427         address_type            TEXT    NOT NULL DEFAULT 'MAILING',
 428         usr                     INT     NOT NULL REFERENCES actor.usr (id),
 429         street1                 TEXT    NOT NULL,
 430         street2                 TEXT,
 431         city                    TEXT    NOT NULL,
 432         county                  TEXT,
 433         state                   TEXT    NOT NULL,
 434         country                 TEXT    NOT NULL,
 435         post_code               TEXT    NOT NULL
 436 );
 437
 438 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 439
 440 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
 441 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
 442
 443 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
 444 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
 445 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
 446
 447
 448 CREATE TABLE actor.org_address (
 449         id              SERIAL  PRIMARY KEY,
 450         valid           BOOL    NOT NULL DEFAULT TRUE,
 451         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 452         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id),
 453         street1         TEXT    NOT NULL,
 454         street2         TEXT,
 455         city            TEXT    NOT NULL,
 456         county          TEXT,
 457         state           TEXT    NOT NULL,
 458         country         TEXT    NOT NULL,
 459         post_code       TEXT    NOT NULL
 460 );
 461
 462 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 463
 464 INSERT INTO actor.org_address VALUES (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303');
 465 UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1;
 466
 467 COMMIT;