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