1 DROP SCHEMA actor CASCADE;
6 CREATE TABLE actor.usr (
8 usrid TEXT NOT NULL UNIQUE, -- barcode
9 usrname TEXT NOT NULL UNIQUE,
10 email TEXT CHECK (email ~ $re$^[[:alnum:]_\.]+@[[:alnum:]_]+(?:\.[[:alnum:]_])+$$re$),
13 first_given_name TEXT NOT NULL,
14 second_given_name TEXT,
15 family_name TEXT NOT NULL,
19 gender CHAR(1) NOT NULL CHECK ( LOWER(gender) IN ('m','f') ),
21 active BOOL NOT NULL DEFAULT TRUE,
22 master_account BOOL NOT NULL DEFAULT FALSE,
23 super_user BOOL NOT NULL DEFAULT FALSE,
24 usrgoup SERIAL NOT NULL,
25 last_xact_id TEXT NOT NULL DEFAULT 'none'
29 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
31 NEW.passwd = MD5( NEW.passwd );
36 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
38 IF NEW.passwd <> OLD.passwd THEN
39 NEW.passwd = MD5( NEW.passwd );
45 CREATE TRIGGER actor_crypt_pw_update_trigger
46 BEFORE UPDATE ON actor.usr FOR EACH ROW
47 EXECUTE PROCEDURE actor.crypt_pw_update ();
49 CREATE TRIGGER actor_crypt_pw_insert_trigger
50 BEFORE INSERT ON actor.usr FOR EACH ROW
51 EXECUTE PROCEDURE actor.crypt_pw_insert ();
53 INSERT INTO actor.usr ( usrid, usrname, passwd, first_given_name, family_name, gender, dob, master_account, super_user )
54 VALUES ( 'admin', 'admin', 'open-ils', 'Administrator', '', 'm', '1979-01-22', TRUE, TRUE );
56 CREATE TABLE actor.org_unit_type (
57 id SERIAL PRIMARY KEY,
61 can_have_users BOOL NOT NULL DEFAULT TRUE
64 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Consortium', 0, NULL, FALSE );
65 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'System', 1, 1, FALSE );
66 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Branch', 2, 2, TRUE );
67 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Sub-lib', 3, 3, TRUE );
69 CREATE TABLE actor.org_unit (
70 id SERIAL PRIMARY KEY,
78 CREATE TABLE actor.usr_access_entry (
79 id BIGSERIAL PRIMARY KEY,
82 CONSTRAINT usr_once_per_ou UNIQUE (usr,org_unit)
86 CREATE TABLE actor.perm_group (
87 id SERIAL PRIMARY KEY,
92 CREATE TABLE actor.permission (
93 id SERIAL PRIMARY KEY,
94 name TEXT NOT NULL UNIQUE,
95 code TEXT NOT NULL UNIQUE
98 CREATE TABLE actor.perm_group_permission_map (
101 CONSTRAINT perm_once_per_group PRIMARY KEY (permission, perm_group)
104 CREATE TABLE actor.perm_group_usr_map (
107 CONSTRAINT usr_once_per_group PRIMARY KEY (usr, perm_group)
110 CREATE TABLE actor.usr_address (
111 id SERIAL PRIMARY KEY,
112 valid BOOL NOT NULL DEFAULT TRUE,
114 street1 TEXT NOT NULL,
116 county TEXT NOT NULL,
118 country TEXT NOT NULL,
119 post_code TEXT NOT NULL