Open-ILS/src/sql/Postgres/005.schema.actors.sql

   1 DROP SCHEMA actor CASCADE;
   2
   3 BEGIN;
   4 CREATE SCHEMA actor;
   5
   6 CREATE TABLE actor.usr (
   7         id                      SERIAL          PRIMARY KEY,
   8         card                    INT             UNIQUE, -- active card
   9         profile                 INT             NOT NULL, -- patron profile
  10         usrid                   TEXT            NOT NULL UNIQUE,
  11         usrname                 TEXT            NOT NULL UNIQUE,
  12         email                   TEXT            CHECK (email ~ $re$^[[:alnum:]_\.]+@[[:alnum:]_]+(?:\.[[:alnum:]_])+$$re$),
  13         passwd                  TEXT            NOT NULL,
  14         standing                INT             NOT NULL DEFAULT 1 REFERENCES config.standing (id),
  15         ident_type              INT             NOT NULL REFERENCES config.identification_type (id),
  16         ident_value             TEXT            NOT NULL,
  17         prefix                  TEXT,
  18         first_given_name        TEXT            NOT NULL,
  19         second_given_name       TEXT,
  20         family_name             TEXT            NOT NULL,
  21         suffix                  TEXT,
  22         day_phone               TEXT,
  23         evening_phone           TEXT,
  24         other_phone             TEXT,
  25         mailing_address         INT,
  26         billing_address         INT,
  27         home_ou                 INT,
  28         gender                  CHAR(1)         NOT NULL CHECK ( LOWER(gender) IN ('m','f') ),
  29         dob                     DATE            NOT NULL,
  30         active                  BOOL            NOT NULL DEFAULT TRUE,
  31         master_account          BOOL            NOT NULL DEFAULT FALSE,
  32         super_user              BOOL            NOT NULL DEFAULT FALSE,
  33         usrgroup                SERIAL          NOT NULL,
  34         claims_returned_count   INT             NOT NULL DEFAULT 0,
  35         credit_forward_balance  NUMERIC(6,2)    NOT NULL DEFAULT 0.00,
  36         last_xact_id            TEXT            NOT NULL DEFAULT 'none',
  37         alert_message           TEXT,
  38         create_date             DATE            NOT NULL DEFAULT now()::DATE,
  39         expire_date             DATE            NOT NULL DEFAULT (now() + '3 years'::INTERVAL)::DATE
  40 );
  41 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
  42 CREATE INDEX actor_usr_address_idx ON actor.usr (address);
  43
  44 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
  45         BEGIN
  46                 NEW.passwd = MD5( NEW.passwd );
  47                 RETURN NEW;
  48         END;
  49 $$ LANGUAGE PLPGSQL;
  50
  51 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
  52         BEGIN
  53                 IF NEW.passwd <> OLD.passwd THEN
  54                         NEW.passwd = MD5( NEW.passwd );
  55                 END IF;
  56                 RETURN NEW;
  57         END;
  58 $$ LANGUAGE PLPGSQL;
  59
  60 CREATE TRIGGER actor_crypt_pw_update_trigger
  61         BEFORE UPDATE ON actor.usr FOR EACH ROW
  62         EXECUTE PROCEDURE actor.crypt_pw_update ();
  63
  64 CREATE TRIGGER actor_crypt_pw_insert_trigger
  65         BEFORE INSERT ON actor.usr FOR EACH ROW
  66         EXECUTE PROCEDURE actor.crypt_pw_insert ();
  67
  68 -- Just so that there is a user...
  69 INSERT INTO actor.usr ( profile, card, usrid, usrname, passwd, first_given_name, family_name, gender, dob, master_account, super_user, ident_type, ident_value )
  70         VALUES ( 3, 1,'admin', 'admin', 'open-ils', 'Administrator', '', 'm', '1979-01-22', TRUE, TRUE, 1, 'identification' );
  71 INSERT INTO actor.usr ( profile, card, usrid, usrname, passwd, first_given_name, family_name, gender, dob, master_account, super_user, ident_type, ident_value )
  72         VALUES ( 2, 2,'demo', 'demo', 'demo', 'demo', 'user', 'm', '1979-01-22', FALSE, TRUE, 1, 'identification' );
  73 INSERT INTO actor.usr ( profile, card, usrid, usrname, passwd, first_given_name, family_name, gender, dob, master_account, super_user, ident_type, ident_value )
  74         VALUES ( 1, 3,'athens', 'athens', 'athens', 'athens', 'user', 'm', '1979-01-22', FALSE, TRUE, 1, 'identification' );
  75
  76 CREATE TABLE actor.profile (
  77         id              SERIAL  PRIMARY KEY,
  78         name            TEXT    NOT NULL UNIQUE
  79 );
  80 INSERT INTO actor.profile (name) VALUES ('ADULT');
  81 INSERT INTO actor.profile (name) VALUES ('JUVENILE');
  82 INSERT INTO actor.profile (name) VALUES ('STAFF');
  83
  84 CREATE TABLE actor.stat_cat (
  85         id              SERIAL  PRIMARY KEY,
  86         owner           INT     NOT NULL, -- actor.org_unit.id
  87         name            TEXT    NOT NULL,
  88         opac_visible    BOOL NOT NULL DEFAULT FALSE,
  89         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
  90 );
  91
  92 CREATE TABLE actor.stat_cat_entry (
  93         id      SERIAL  PRIMARY KEY,
  94         owner   INT     NOT NULL, -- actor.org_unit.id
  95         value   TEXT    NOT NULL,
  96         CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
  97 );
  98
  99 CREATE TABLE actor.stat_cat_entry_usr_map (
 100         id              BIGSERIAL       PRIMARY KEY,
 101         stat_cat_entry  INT             NOT NULL REFERENCES actor.stat_cat_entry (id) ON DELETE CASCADE,
 102         target_usr      INT             NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
 103         CONSTRAINT sce_once_per_copy UNIQUE (target_usr,stat_cat_entry)
 104 );
 105 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
 106
 107 CREATE TABLE actor.card (
 108         id      SERIAL  PRIMARY KEY,
 109         usr     INT     NOT NULL REFERENCES actor.usr (id),
 110         barcode TEXT    NOT NULL UNIQUE,
 111         active  BOOL    NOT NULL DEFAULT TRUE
 112 );
 113 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
 114
 115 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
 116 INSERT INTO actor.card (usr, barcode) VALUES (2,'101010101010102');
 117 INSERT INTO actor.card (usr, barcode) VALUES (3,'101010101010103');
 118
 119
 120 CREATE TABLE actor.org_unit_type (
 121         id              SERIAL  PRIMARY KEY,
 122         name            TEXT    NOT NULL,
 123         depth           INT     NOT NULL,
 124         parent          INT     REFERENCES actor.org_unit_type (id),
 125         can_have_vols   BOOL    NOT NULL DEFAULT TRUE,
 126         can_have_users  BOOL    NOT NULL DEFAULT TRUE
 127 );
 128 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
 129
 130 -- The PINES levels
 131 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium', 0, NULL, FALSE, FALSE );
 132 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users, can_have_vols) VALUES ( 'System', 1, 1, FALSE, FALSE );
 133 INSERT INTO actor.org_unit_type (name, depth, parent) VALUES ( 'Branch', 2, 2 );
 134 INSERT INTO actor.org_unit_type (name, depth, parent) VALUES ( 'Sub-lib', 5, 3 );
 135
 136 CREATE TABLE actor.org_unit (
 137         id              SERIAL  PRIMARY KEY,
 138         parent_ou       INT     REFERENCES actor.org_unit (id),
 139         ou_type         INT     NOT NULL REFERENCES actor.org_unit_type (id),
 140         ill_address     INT,
 141         holds_address   INT,
 142         mailing_address INT,
 143         billing_address INT,
 144         shortname       TEXT    NOT NULL,
 145         name            TEXT    NOT NULL
 146 );
 147 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
 148 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
 149 CREATE INDEX actor_org_unit_address_idx ON actor.org_unit (address);
 150
 151 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'PINES', 'Georgia PINES Consortium');
 152
 153 CREATE TABLE actor.usr_access_entry (
 154         id              BIGSERIAL       PRIMARY KEY,
 155         usr             INT             NOT NULL REFERENCES actor.usr (id),
 156         org_unit        INT             NOT NULL REFERENCES actor.org_unit (id),
 157         CONSTRAINT usr_once_per_ou UNIQUE (usr,org_unit)
 158 );
 159
 160
 161 CREATE TABLE actor.perm_group (
 162         id      SERIAL  PRIMARY KEY,
 163         name    TEXT    NOT NULL,
 164         ou_type INT     NOT NULL REFERENCES actor.org_unit_type (id)
 165 );
 166
 167 CREATE TABLE actor.permission (
 168         id              SERIAL  PRIMARY KEY,
 169         name            TEXT    NOT NULL UNIQUE,
 170         code            TEXT    NOT NULL UNIQUE
 171 );
 172
 173 CREATE TABLE actor.perm_group_permission_map (
 174         id              SERIAL  PRIMARY KEY,
 175         permission      INT     NOT NULL REFERENCES actor.permission (id),
 176         perm_group      INT     NOT NULL REFERENCES actor.perm_group (id),
 177         CONSTRAINT perm_once_per_group UNIQUE (permission, perm_group)
 178 );
 179
 180 CREATE TABLE actor.perm_group_usr_map (
 181         id              BIGSERIAL       PRIMARY KEY,
 182         usr             INT             NOT NULL REFERENCES actor.usr (id),
 183         perm_group      INT             NOT NULL REFERENCES actor.perm_group (id),
 184         CONSTRAINT usr_once_per_group UNIQUE (usr, perm_group)
 185 );
 186
 187 CREATE TABLE actor.usr_address (
 188         id              SERIAL  PRIMARY KEY,
 189         valid           BOOL    NOT NULL DEFAULT TRUE,
 190         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 191         usr             INT     NOT NULL REFERENCES actor.usr (id),
 192         street1         TEXT    NOT NULL,
 193         street2         TEXT,
 194         county          TEXT,
 195         state           TEXT    NOT NULL,
 196         country         TEXT    NOT NULL,
 197         post_code       TEXT    NOT NULL
 198 );
 199
 200 CREATE TABLE actor.org_address (
 201         id              SERIAL  PRIMARY KEY,
 202         valid           BOOL    NOT NULL DEFAULT TRUE,
 203         address_type    TEXT    NOT NULL DEFAULT 'MAILING',
 204         org_unit        INT     NOT NULL REFERENCES actor.org_unit (id),
 205         street1         TEXT    NOT NULL,
 206         street2         TEXT,
 207         county          TEXT,
 208         state           TEXT    NOT NULL,
 209         country         TEXT    NOT NULL,
 210         post_code       TEXT    NOT NULL
 211 );
 212
 213
 214 COMMIT;