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