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