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