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