Open-ILS/src/sql/Postgres/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,
  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 )
 133         VALUES ( 3, 1,'admin', 'open-ils', 'Administrator', '', '1979-01-22', TRUE, TRUE, 1, 'identification' );
 134 INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value )
 135         VALUES ( 2, 2,'demo', 'demo', 'demo', 'user', '1979-01-22', FALSE, TRUE, 1, 'identification' );
 136 INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value )
 137         VALUES ( 1, 3,'athens', 'athens', 'athens', 'user', '1979-01-22', FALSE, TRUE, 1, 'identification' );
 138
 139 CREATE TABLE actor.profile (
 140         id              SERIAL  PRIMARY KEY,
 141         name            TEXT    NOT NULL UNIQUE
 142 );
 143 INSERT INTO actor.profile (name) VALUES ('ADULT');
 144 INSERT INTO actor.profile (name) VALUES ('JUVENILE');
 145 INSERT INTO actor.profile (name) VALUES ('STAFF');
 146
 147 CREATE TABLE actor.stat_cat (
 148         id              SERIAL  PRIMARY KEY,
 149         owner           INT     NOT NULL,
 150         name            TEXT    NOT NULL,
 151         opac_visible    BOOL NOT NULL DEFAULT FALSE,
 152         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 153 );
 154 COMMENT ON TABLE actor.stat_cat IS $$
 155 /*
 156  * Copyright (C) 2005  Georgia Public Library Service
 157  * Mike Rylander <mrylander@gmail.com>
 158  *
 159  * User Statistical Catagories
 160  *
 161  * Local data collected about Users is placed into a Statistical
 162  * Catagory.  Here's where those catagories are defined.
 163  *
 164  * ****
 165  *
 166  * This program is free software; you can redistribute it and/or
 167  * modify it under the terms of the GNU General Public License
 168  * as published by the Free Software Foundation; either version 2
 169  * of the License, or (at your option) any later version.
 170  *
 171  * This program is distributed in the hope that it will be useful,
 172  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 173  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 174  * GNU General Public License for more details.
 175  */
 176 $$;
 177
 178
 179 CREATE TABLE actor.stat_cat_entry (
 180         id              SERIAL  PRIMARY KEY,
 181         stat_cat        INT     NOT NULL,
 182         owner           INT     NOT NULL,
 183         value           TEXT    NOT NULL,
 184         CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
 185 );
 186 COMMENT ON TABLE actor.stat_cat_entry IS $$
 187 /*
 188  * Copyright (C) 2005  Georgia Public Library Service
 189  * Mike Rylander <mrylander@gmail.com>
 190  *
 191  * User Statistical Catagory Entries
 192  *
 193  * Local data collected about Users is placed into a Statistical
 194  * Catagory.  Each library can create entries into any of it's own
 195  * stat_cats, it's anscestors stat_cats, or it's descendants' stat_cats.
 196  *
 197  *
 198  * ****
 199  *
 200  * This program is free software; you can redistribute it and/or
 201  * modify it under the terms of the GNU General Public License
 202  * as published by the Free Software Foundation; either version 2
 203  * of the License, or (at your option) any later version.
 204  *
 205  * This program is distributed in the hope that it will be useful,
 206  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 207  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 208  * GNU General Public License for more details.
 209  */
 210 $$;
 211
 212
 213 CREATE TABLE actor.stat_cat_entry_usr_map (
 214         id              BIGSERIAL       PRIMARY KEY,
 215         stat_cat_entry  TEXT            NOT NULL,
 216         stat_cat        INT             NOT NULL,
 217         target_usr      INT             NOT NULL,
 218         CONSTRAINT sce_once_per_copy UNIQUE (target_usr,stat_cat)
 219 );
 220 COMMENT ON TABLE actor.stat_cat_entry_usr_map IS $$
 221 /*
 222  * Copyright (C) 2005  Georgia Public Library Service
 223  * Mike Rylander <mrylander@gmail.com>
 224  *
 225  * Statistical Catagory Entry to User map
 226  *
 227  * Records the stat_cat entries for each user.
 228  *
 229  *
 230  * ****
 231  *
 232  * This program is free software; you can redistribute it and/or
 233  * modify it under the terms of the GNU General Public License
 234  * as published by the Free Software Foundation; either version 2
 235  * of the License, or (at your option) any later version.
 236  *
 237  * This program is distributed in the hope that it will be useful,
 238  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 239  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 240  * GNU General Public License for more details.
 241  */
 242 $$;
 243
 244 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 245
 246 CREATE TABLE actor.card (
 247         id      SERIAL  PRIMARY KEY,
 248         usr     INT     NOT NULL REFERENCES actor.usr (id),
 249         barcode TEXT    NOT NULL UNIQUE,
 250         active  BOOL    NOT NULL DEFAULT TRUE
 251 );
 252 COMMENT ON TABLE actor.card IS $$
 253 /*
 254  * Copyright (C) 2005  Georgia Public Library Service
 255  * Mike Rylander <mrylander@gmail.com>
 256  *
 257  * Library Cards
 258  *
 259  * Each User has one or more library cards.  The current "main"
 260  * card is linked to here from the actor.usr table, and it is up
 261  * to the consortium policy whether more than one card can be
 262  * active for any one user at a given time.
 263  *
 264  *
 265  * ****
 266  *
 267  * This program is free software; you can redistribute it and/or
 268  * modify it under the terms of the GNU General Public License
 269  * as published by the Free Software Foundation; either version 2
 270  * of the License, or (at your option) any later version.
 271  *
 272  * This program is distributed in the hope that it will be useful,
 273  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 274  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 275  * GNU General Public License for more details.
 276  */
 277 $$;
 278
 279 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 280
 281 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
 282 INSERT INTO actor.card (usr, barcode) VALUES (2,'101010101010102');
 283 INSERT INTO actor.card (usr, barcode) VALUES (3,'101010101010103');
 284
 285
 286 CREATE TABLE actor.org_unit_type (
 287         id              SERIAL  PRIMARY KEY,
 288         name            TEXT    NOT NULL,
 289         opac_label      TEXT    NOT NULL,
 290         depth           INT     NOT NULL,
 291         parent          INT     REFERENCES actor.org_unit_type (id),
 292         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 293         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 294 );
 295 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 296
 297 -- The PINES levels
 298 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium','All of PINES', 0, NULL, FALSE, FALSE );
 299 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 );
 300 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Branch','This Branch', 2, 2 );
 301 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Sub-lib','This Specialized Library', 3, 3 );
 302 INSERT INTO actor.org_unit_type (name, opac_label, depth, parent) VALUES ( 'Bookmobile','Your Bookmobile', 3, 3 );
 303
 304 CREATE TABLE actor.org_unit (
 305         id              SERIAL  PRIMARY KEY,
 306         parent_ou       INT     REFERENCES actor.org_unit (id),
 307         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id),
 308         ill_address     INT,
 309         holds_address   INT,
 310         mailing_address INT,
 311         billing_address INT,
 312         shortname       TEXT    NOT NULL,
 313         name            TEXT    NOT NULL
 314 );
 315 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 316 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 317 CREATE INDEX actor_org_unit_ill_address_idx ON actor.org_unit (ill_address);
 318 CREATE INDEX actor_org_unit_billing_address_idx ON actor.org_unit (billing_address);
 319 CREATE INDEX actor_org_unit_mailing_address_idx ON actor.org_unit (mailing_address);
 320 CREATE INDEX actor_org_unit_holds_address_idx ON actor.org_unit (holds_address);
 321
 322 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'PINES', 'Georgia PINES Consortium');
 323
 324 CREATE TABLE actor.usr_access_entry (
 325         id              BIGSERIAL       PRIMARY KEY,
 326         usr             INT             NOT NULL REFERENCES actor.usr (id),
 327         org_unit        INT             NOT NULL REFERENCES actor.org_unit (id),
 328         CONSTRAINT usr_once_per_ou UNIQUE (usr,org_unit)
 329 );
 330
 331 CREATE TABLE actor.usr_address (
 332         id              SERIAL  PRIMARY KEY,
 333         valid           BOOL    NOT NULL DEFAULT TRUE,
 334         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 335         usr             INT     NOT NULL REFERENCES actor.usr (id),
 336         street1         TEXT    NOT NULL,
 337         street2         TEXT,
 338         city            TEXT    NOT NULL,
 339         county          TEXT,
 340         state           TEXT    NOT NULL,
 341         country         TEXT    NOT NULL,
 342         post_code       TEXT    NOT NULL
 343 );
 344
 345 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (lower(street1));
 346 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (lower(street2));
 347
 348 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (lower(city));
 349 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (lower(state));
 350 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (lower(post_code));
 351
 352
 353 CREATE TABLE actor.org_address (
 354         id              SERIAL  PRIMARY KEY,
 355         valid           BOOL    NOT NULL DEFAULT TRUE,
 356         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 357         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id),
 358         street1         TEXT    NOT NULL,
 359         street2         TEXT,
 360         city            TEXT    NOT NULL,
 361         county          TEXT,
 362         state           TEXT    NOT NULL,
 363         country         TEXT    NOT NULL,
 364         post_code       TEXT    NOT NULL
 365 );
 366
 367
 368 COMMIT;