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