1 DROP SCHEMA actor CASCADE;
6 CREATE TABLE actor.usr (
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$),
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),
19 net_access_level INT NOT NULL DEFAULT 1 REFERENCES config.net_access_level (id),
22 first_given_name TEXT NOT NULL,
23 second_given_name TEXT,
24 family_name TEXT NOT NULL,
32 gender CHAR(1) NOT NULL CHECK ( LOWER(gender) IN ('m','f') ),
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',
42 create_date DATE NOT NULL DEFAULT now()::DATE,
43 expire_date DATE NOT NULL DEFAULT (now() + '3 years'::INTERVAL)::DATE
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);
49 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
51 NEW.passwd = MD5( NEW.passwd );
56 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
58 IF NEW.passwd <> OLD.passwd THEN
59 NEW.passwd = MD5( NEW.passwd );
65 CREATE TRIGGER actor_crypt_pw_update_trigger
66 BEFORE UPDATE ON actor.usr FOR EACH ROW
67 EXECUTE PROCEDURE actor.crypt_pw_update ();
69 CREATE TRIGGER actor_crypt_pw_insert_trigger
70 BEFORE INSERT ON actor.usr FOR EACH ROW
71 EXECUTE PROCEDURE actor.crypt_pw_insert ();
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' );
81 CREATE TABLE actor.profile (
82 id SERIAL PRIMARY KEY,
83 name TEXT NOT NULL UNIQUE
85 INSERT INTO actor.profile (name) VALUES ('ADULT');
86 INSERT INTO actor.profile (name) VALUES ('JUVENILE');
87 INSERT INTO actor.profile (name) VALUES ('STAFF');
89 CREATE TABLE actor.stat_cat (
90 id SERIAL PRIMARY KEY,
91 owner INT NOT NULL, -- actor.org_unit.id
93 opac_visible BOOL NOT NULL DEFAULT FALSE,
94 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
97 CREATE TABLE actor.stat_cat_entry (
98 id SERIAL PRIMARY KEY,
99 owner INT NOT NULL, -- actor.org_unit.id
101 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
104 CREATE TABLE actor.stat_cat_entry_usr_map (
105 id BIGSERIAL PRIMARY KEY,
106 stat_cat_entry TEXT NOT NULL,
107 stat_cat INT NOT NULL REFERENCES actor.stat_cat (id) ON DELETE CASCADE,
108 target_usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE,
109 CONSTRAINT sce_once_per_copy UNIQUE (target_usr,stat_cat)
111 CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr);
113 CREATE TABLE actor.card (
114 id SERIAL PRIMARY KEY,
115 usr INT NOT NULL REFERENCES actor.usr (id),
116 barcode TEXT NOT NULL UNIQUE,
117 active BOOL NOT NULL DEFAULT TRUE
119 CREATE INDEX actor_card_usr_idx ON actor.card (usr);
121 INSERT INTO actor.card (usr, barcode) VALUES (1,'101010101010101');
122 INSERT INTO actor.card (usr, barcode) VALUES (2,'101010101010102');
123 INSERT INTO actor.card (usr, barcode) VALUES (3,'101010101010103');
126 CREATE TABLE actor.org_unit_type (
127 id SERIAL PRIMARY KEY,
130 parent INT REFERENCES actor.org_unit_type (id),
131 can_have_vols BOOL NOT NULL DEFAULT TRUE,
132 can_have_users BOOL NOT NULL DEFAULT TRUE
134 CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent);
137 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium', 0, NULL, FALSE, FALSE );
138 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users, can_have_vols) VALUES ( 'System', 1, 1, FALSE, FALSE );
139 INSERT INTO actor.org_unit_type (name, depth, parent) VALUES ( 'Branch', 2, 2 );
140 INSERT INTO actor.org_unit_type (name, depth, parent) VALUES ( 'Sub-lib', 5, 3 );
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),
150 shortname TEXT NOT NULL,
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);
160 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'PINES', 'Georgia PINES Consortium');
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)
170 CREATE TABLE actor.perm_group (
171 id SERIAL PRIMARY KEY,
173 ou_type INT NOT NULL REFERENCES actor.org_unit_type (id)
176 CREATE TABLE actor.permission (
177 id SERIAL PRIMARY KEY,
178 name TEXT NOT NULL UNIQUE,
179 code TEXT NOT NULL UNIQUE
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)
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)
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,
206 country TEXT NOT NULL,
207 post_code TEXT NOT NULL
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,
220 country TEXT NOT NULL,
221 post_code TEXT NOT NULL