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_standing_penalty (
 137         id              SERIAL  PRIMARY KEY,
 138         usr             INT     NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
 139         penalty_type    TEXT    NOT NULL
 140 );
 141 COMMENT ON TABLE actor.usr_standing_penalty IS $$
 142 /*
 143  * Copyright (C) 2005  Georgia Public Library Service
 144  * Mike Rylander <mrylander@gmail.com>
 145  *
 146  * User standing penalties
 147  *
 148  * ****
 149  *
 150  * This program is free software; you can redistribute it and/or
 151  * modify it under the terms of the GNU General Public License
 152  * as published by the Free Software Foundation; either version 2
 153  * of the License, or (at your option) any later version.
 154  *
 155  * This program is distributed in the hope that it will be useful,
 156  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 157  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 158  * GNU General Public License for more details.
 159  */
 160 $$;
 161
 162 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
 163
 164 CREATE TABLE actor.usr_setting (
 165         id      BIGSERIAL       PRIMARY KEY,
 166         usr     INT             NOT NULL REFERENCES actor.usr ON DELETE CASCADE,
 167         name    TEXT            NOT NULL,
 168         value   TEXT            NOT NULL,
 169         CONSTRAINT usr_once_per_key UNIQUE (usr,name)
 170 );
 171 COMMENT ON TABLE actor.usr_setting IS $$
 172 /*
 173  * Copyright (C) 2005  Georgia Public Library Service
 174  * Mike Rylander <mrylander@gmail.com>
 175  *
 176  * User settings
 177  *
 178  * This table contains any arbitrary settings that a client
 179  * program would like to save for a user.
 180  *
 181  * ****
 182  *
 183  * This program is free software; you can redistribute it and/or
 184  * modify it under the terms of the GNU General Public License
 185  * as published by the Free Software Foundation; either version 2
 186  * of the License, or (at your option) any later version.
 187  *
 188  * This program is distributed in the hope that it will be useful,
 189  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 190  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 191  * GNU General Public License for more details.
 192  */
 193 $$;
 194
 195 CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
 196
 197
 198 CREATE TABLE actor.stat_cat (
 199         id              SERIAL  PRIMARY KEY,
 200         owner           INT     NOT NULL,
 201         name            TEXT    NOT NULL,
 202         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 203         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 204 );
 205 COMMENT ON TABLE actor.stat_cat IS $$
 206 /*
 207  * Copyright (C) 2005  Georgia Public Library Service
 208  * Mike Rylander <mrylander@gmail.com>
 209  *
 210  * User Statistical Catagories
 211  *
 212  * Local data collected about Users is placed into a Statistical
 213  * Catagory.  Here's where those catagories are defined.
 214  *
 215  * ****
 216  *
 217  * This program is free software; you can redistribute it and/or
 218  * modify it under the terms of the GNU General Public License
 219  * as published by the Free Software Foundation; either version 2
 220  * of the License, or (at your option) any later version.
 221  *
 222  * This program is distributed in the hope that it will be useful,
 223  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 224  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 225  * GNU General Public License for more details.
 226  */
 227 $$;
 228
 229
 230 CREATE TABLE actor.stat_cat_entry (
 231         id              SERIAL  PRIMARY KEY,
 232         stat_cat        INT     NOT NULL,
 233         owner           INT     NOT NULL,
 234         value           TEXT    NOT NULL,
 235         CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
 236 );
 237 COMMENT ON TABLE actor.stat_cat_entry IS $$
 238 /*
 239  * Copyright (C) 2005  Georgia Public Library Service
 240  * Mike Rylander <mrylander@gmail.com>
 241  *
 242  * User Statistical Catagory Entries
 243  *
 244  * Local data collected about Users is placed into a Statistical
 245  * Catagory.  Each library can create entries into any of it's own
 246  * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
 247  *
 248  *
 249  * ****
 250  *
 251  * This program is free software; you can redistribute it and/or
 252  * modify it under the terms of the GNU General Public License
 253  * as published by the Free Software Foundation; either version 2
 254  * of the License, or (at your option) any later version.
 255  *
 256  * This program is distributed in the hope that it will be useful,
 257  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 258  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 259  * GNU General Public License for more details.
 260  */
 261 $$;
 262
 263
 264 CREATE TABLE actor.stat_cat_entry_usr_map (
 265         id              BIGSERIAL       PRIMARY KEY,
 266         stat_cat_entry  TEXT            NOT NULL,
 267         stat_cat        INT             NOT NULL,
 268         target_usr      INT             NOT NULL,
 269         CONSTRAINT sc_once_per_usr UNIQUE (target_usr,stat_cat)
 270 );
 271 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 272 /*
 273  * Copyright (C) 2005  Georgia Public Library Service
 274  * Mike Rylander <mrylander@gmail.com>
 275  *
 276  * Statistical Catagory Entry to User map
 277  *
 278  * Records the stat_cat entries for each user.
 279  *
 280  *
 281  * ****
 282  *
 283  * This program is free software; you can redistribute it and/or
 284  * modify it under the terms of the GNU General Public License
 285  * as published by the Free Software Foundation; either version 2
 286  * of the License, or (at your option) any later version.
 287  *
 288  * This program is distributed in the hope that it will be useful,
 289  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 290  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 291  * GNU General Public License for more details.
 292  */
 293 $$;
 294
 295 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 296
 297 CREATE TABLE actor.card (
 298         id      SERIAL  PRIMARY KEY,
 299         usr     INT     NOT NULL REFERENCES actor.usr (id),
 300         barcode TEXT    NOT NULL UNIQUE,
 301         active  BOOL    NOT NULL DEFAULT TRUE
 302 );
 303 COMMENT ON TABLE actor.card IS $$
 304 /*
 305  * Copyright (C) 2005  Georgia Public Library Service
 306  * Mike Rylander <mrylander@gmail.com>
 307  *
 308  * Library Cards
 309  *
 310  * Each User has one or more library cards.  The current "main"
 311  * card is linked to here from the actor.usr table, and it is up
 312  * to the consortium policy whether more than one card can be
 313  * active for any one user at a given time.
 314  *
 315  *
 316  * ****
 317  *
 318  * This program is free software; you can redistribute it and/or
 319  * modify it under the terms of the GNU General Public License
 320  * as published by the Free Software Foundation; either version 2
 321  * of the License, or (at your option) any later version.
 322  *
 323  * This program is distributed in the hope that it will be useful,
 324  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 325  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 326  * GNU General Public License for more details.
 327  */
 328 $$;
 329
 330 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 331
 332 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
 333
 334
 335 CREATE TABLE actor.org_unit_type (
 336         id              SERIAL  PRIMARY KEY,
 337         name            TEXT    NOT NULL,
 338         opac_label      TEXT    NOT NULL,
 339         depth           INT     NOT NULL,
 340         parent          INT     REFERENCES actor.org_unit_type (id),
 341         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 342         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 343 );
 344 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 345
 346 -- The PINES levels
 347 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','Everywhere', 0, NULL, FALSE, FALSE );
 348 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 );
 349 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
 350 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
 351 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
 352
 353 CREATE TABLE actor.org_unit (
 354         id              SERIAL  PRIMARY KEY,
 355         parent_ou       INT     REFERENCES actor.org_unit (id),
 356         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id),
 357         ill_address     INT,
 358         holds_address   INT,
 359         mailing_address INT,
 360         billing_address INT,
 361         shortname       TEXT    NOT NULL,
 362         name            TEXT    NOT NULL
 363 );
 364 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 365 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 366 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 367 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 368 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 369 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 370
 371 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'CONS', 'Example Consortium');
 372 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS1', 'Example System 1');
 373 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (1, 2, 'SYS2', 'Example System 2');
 374 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR1', 'Example Branch 1');
 375 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (2, 3, 'BR2', 'Example Branch 2');
 376 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR3', 'Example Branch 3');
 377 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (3, 3, 'BR4', 'Example Branch 4');
 378 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (4, 4, 'SL1', 'Example Sub-lib 1');
 379 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (6, 5, 'BM1', 'Example Bookmobile 1');
 380
 381 CREATE TABLE actor.org_unit_setting (
 382         id              BIGSERIAL       PRIMARY KEY,
 383         org_unit        INT             NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE,
 384         name            TEXT            NOT NULL,
 385         value           TEXT            NOT NULL,
 386         CONSTRAINT ou_once_per_key UNIQUE (org_unit,name)
 387 );
 388 COMMENT ON TABLE actor.org_unit_setting IS $$
 389 /*
 390  * Copyright (C) 2005  Georgia Public Library Service
 391  * Mike Rylander <mrylander@gmail.com>
 392  *
 393  * Org Unit settings
 394  *
 395  * This table contains any arbitrary settings that a client
 396  * program would like to save for an org unit.
 397  *
 398  * ****
 399  *
 400  * This program is free software; you can redistribute it and/or
 401  * modify it under the terms of the GNU General Public License
 402  * as published by the Free Software Foundation; either version 2
 403  * of the License, or (at your option) any later version.
 404  *
 405  * This program is distributed in the hope that it will be useful,
 406  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 407  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 408  * GNU General Public License for more details.
 409  */
 410 $$;
 411
 412 CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
 413
 414
 415 CREATE TABLE actor.usr_address (
 416         id              SERIAL  PRIMARY KEY,
 417         valid           BOOL    NOT NULL DEFAULT TRUE,
 418         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 419         usr             INT     NOT NULL REFERENCES actor.usr (id),
 420         street1         TEXT    NOT NULL,
 421         street2         TEXT,
 422         city            TEXT    NOT NULL,
 423         county          TEXT,
 424         state           TEXT    NOT NULL,
 425         country         TEXT    NOT NULL,
 426         post_code       TEXT    NOT NULL
 427 );
 428
 429 CREATE INDEX actor_usr_addr_usr_idx ON actor.usr_address (usr);
 430
 431 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
 432 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
 433
 434 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
 435 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
 436 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
 437
 438
 439 CREATE TABLE actor.org_address (
 440         id              SERIAL  PRIMARY KEY,
 441         valid           BOOL    NOT NULL DEFAULT TRUE,
 442         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 443         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id),
 444         street1         TEXT    NOT NULL,
 445         street2         TEXT,
 446         city            TEXT    NOT NULL,
 447         county          TEXT,
 448         state           TEXT    NOT NULL,
 449         country         TEXT    NOT NULL,
 450         post_code       TEXT    NOT NULL
 451 );
 452
 453 CREATE INDEX actor_org_address_org_unit_idx ON actor.org_address (org_unit);
 454
 455 INSERT INTO actor.org_address VALUES (DEFAULT,DEFAULT,DEFAULT,1,'123 Main St.',NULL,'Anywhere',NULL,'GA','US','30303');
 456 UPDATE actor.org_unit SET holds_address = 1, ill_address = 1, billing_address = 1, mailing_address = 1;
 457
 458 COMMIT;