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'
28 CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
29 CREATE INDEX actor_usr_address_idx ON actor.usr (address);
31 CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
33 NEW.passwd = MD5( NEW.passwd );
38 CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
40 IF NEW.passwd <> OLD.passwd THEN
41 NEW.passwd = MD5( NEW.passwd );
47 CREATE TRIGGER actor_crypt_pw_update_trigger
48 BEFORE UPDATE ON actor.usr FOR EACH ROW
49 EXECUTE PROCEDURE actor.crypt_pw_update ();
51 CREATE TRIGGER actor_crypt_pw_insert_trigger
52 BEFORE INSERT ON actor.usr FOR EACH ROW
53 EXECUTE PROCEDURE actor.crypt_pw_insert ();
55 -- Just so that there is a user...
56 INSERT INTO actor.usr ( usrid, usrname, passwd, first_given_name, family_name, gender, dob, master_account, super_user )
57 VALUES ( 'admin', 'admin', 'open-ils', 'Administrator', '', 'm', '1979-01-22', TRUE, TRUE );
59 CREATE TABLE actor.org_unit_type (
60 id SERIAL PRIMARY KEY,
64 can_have_users BOOL NOT NULL DEFAULT TRUE
68 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Consortium', 0, NULL, FALSE );
69 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'System', 1, 1, FALSE );
70 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Branch', 2, 2, TRUE );
71 INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Sub-lib', 5, 3, TRUE );
73 CREATE TABLE actor.org_unit (
74 id SERIAL PRIMARY KEY,
78 shortname TEXT NOT NULL,
81 CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
82 CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
83 CREATE INDEX actor_org_unit_address_idx ON actor.org_unit (address);
85 -- Some PINES test libraries
86 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'PINES', 'Georgia PINES Consortium');
88 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 2, 'ARL', 'Athens Regional Library System');
89 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'ARL-ATH', 'Athens-Clark County Library');
90 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'ARL-BOG', 'Bogart Branch Library');
92 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 2, 'MGRL', 'Middle Georgia Regional Library System');
93 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'MGRL-RC', 'Rocky Creek Branch Library');
94 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'MGRL-WA', 'Washington Memorial Library');
95 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 4, 'MGRL-MM', 'Bookmobile');
97 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 2, 'HOU', 'Houston County Library System');
98 INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'HOU-WR', 'Nola Brantley Memorial Library');
100 CREATE TABLE actor.usr_access_entry (
101 id BIGSERIAL PRIMARY KEY,
104 CONSTRAINT usr_once_per_ou UNIQUE (usr,org_unit)
108 CREATE TABLE actor.perm_group (
109 id SERIAL PRIMARY KEY,
114 CREATE TABLE actor.permission (
115 id SERIAL PRIMARY KEY,
116 name TEXT NOT NULL UNIQUE,
117 code TEXT NOT NULL UNIQUE
120 CREATE TABLE actor.perm_group_permission_map (
123 CONSTRAINT perm_once_per_group PRIMARY KEY (permission, perm_group)
126 CREATE TABLE actor.perm_group_usr_map (
129 CONSTRAINT usr_once_per_group PRIMARY KEY (usr, perm_group)
132 CREATE TABLE actor.usr_address (
133 id SERIAL PRIMARY KEY,
134 valid BOOL NOT NULL DEFAULT TRUE,
136 street1 TEXT NOT NULL,
138 county TEXT NOT NULL,
140 country TEXT NOT NULL,
141 post_code TEXT NOT NULL