1 -- Evergreen DB patch 0681.schema.user-activity.sql
5 -- check whether patch can be applied
6 SELECT evergreen.upgrade_deps_block_check('0681', :eg_version);
10 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
12 CREATE TABLE config.usr_activity_type (
13 id SERIAL PRIMARY KEY,
17 label TEXT NOT NULL, -- i18n
18 egroup config.usr_activity_group NOT NULL,
19 enabled BOOL NOT NULL DEFAULT TRUE,
20 transient BOOL NOT NULL DEFAULT FALSE,
21 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
24 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
25 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
27 CREATE TABLE actor.usr_activity (
28 id BIGSERIAL PRIMARY KEY,
29 usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
30 etype INT NOT NULL REFERENCES config.usr_activity_type (id),
31 event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
34 -- remove transient activity entries on insert of new entries
35 CREATE OR REPLACE FUNCTION actor.usr_activity_transient_trg () RETURNS TRIGGER AS $$
37 DELETE FROM actor.usr_activity act USING config.usr_activity_type atype
38 WHERE atype.transient AND
39 NEW.etype = atype.id AND
40 act.etype = atype.id AND
46 CREATE TRIGGER remove_transient_usr_activity
47 BEFORE INSERT ON actor.usr_activity
48 FOR EACH ROW EXECUTE PROCEDURE actor.usr_activity_transient_trg();
50 -- given a set of activity criteria, find the most approprate activity type
51 CREATE OR REPLACE FUNCTION actor.usr_activity_get_type (
55 ) RETURNS SETOF config.usr_activity_type AS $$
56 SELECT * FROM config.usr_activity_type
59 (ewho IS NULL OR ewho = $1) AND
60 (ewhat IS NULL OR ewhat = $2) AND
61 (ehow IS NULL OR ehow = $3)
63 -- BOOL comparisons sort false to true
64 COALESCE(ewho, '') != COALESCE($1, ''),
65 COALESCE(ewhat,'') != COALESCE($2, ''),
66 COALESCE(ehow, '') != COALESCE($3, '')
70 -- given a set of activity criteria, finds the best
71 -- activity type and inserts the activity entry
72 CREATE OR REPLACE FUNCTION actor.insert_usr_activity (
77 ) RETURNS SETOF actor.usr_activity AS $$
79 new_row actor.usr_activity%ROWTYPE;
81 SELECT id INTO new_row.etype FROM actor.usr_activity_get_type(ewho, ewhat, ehow);
84 INSERT INTO actor.usr_activity (usr, etype)
85 VALUES (usr, new_row.etype)
86 RETURNING * INTO new_row;
94 INSERT INTO config.usr_activity_type (id, ewho, ewhat, ehow, egroup, label) VALUES
96 -- authen/authz actions
97 -- note: "opensrf" is the default ingress/ehow
98 (1, NULL, 'login', 'opensrf', 'authen', oils_i18n_gettext(1 , 'Login via opensrf', 'cuat', 'label'))
99 ,(2, NULL, 'login', 'srfsh', 'authen', oils_i18n_gettext(2 , 'Login via srfsh', 'cuat', 'label'))
100 ,(3, NULL, 'login', 'gateway-v1', 'authen', oils_i18n_gettext(3 , 'Login via gateway-v1', 'cuat', 'label'))
101 ,(4, NULL, 'login', 'translator-v1','authen', oils_i18n_gettext(4 , 'Login via translator-v1', 'cuat', 'label'))
102 ,(5, NULL, 'login', 'xmlrpc', 'authen', oils_i18n_gettext(5 , 'Login via xmlrpc', 'cuat', 'label'))
103 ,(6, NULL, 'login', 'remoteauth', 'authen', oils_i18n_gettext(6 , 'Login via remoteauth', 'cuat', 'label'))
104 ,(7, NULL, 'login', 'sip2', 'authen', oils_i18n_gettext(7 , 'SIP2 Proxy Login', 'cuat', 'label'))
105 ,(8, NULL, 'login', 'apache', 'authen', oils_i18n_gettext(8 , 'Login via Apache module', 'cuat', 'label'))
107 ,(9, NULL, 'verify', 'opensrf', 'authz', oils_i18n_gettext(9 , 'Verification via opensrf', 'cuat', 'label'))
108 ,(10, NULL, 'verify', 'srfsh', 'authz', oils_i18n_gettext(10, 'Verification via srfsh', 'cuat', 'label'))
109 ,(11, NULL, 'verify', 'gateway-v1', 'authz', oils_i18n_gettext(11, 'Verification via gateway-v1', 'cuat', 'label'))
110 ,(12, NULL, 'verify', 'translator-v1','authz', oils_i18n_gettext(12, 'Verification via translator-v1', 'cuat', 'label'))
111 ,(13, NULL, 'verify', 'xmlrpc', 'authz', oils_i18n_gettext(13, 'Verification via xmlrpc', 'cuat', 'label'))
112 ,(14, NULL, 'verify', 'remoteauth', 'authz', oils_i18n_gettext(14, 'Verification via remoteauth', 'cuat', 'label'))
113 ,(15, NULL, 'verify', 'sip2', 'authz', oils_i18n_gettext(15, 'SIP2 User Verification', 'cuat', 'label'))
115 -- authen/authz actions w/ known uses of "who"
116 ,(16, 'opac', 'login', 'gateway-v1', 'authen', oils_i18n_gettext(16, 'OPAC Login (jspac)', 'cuat', 'label'))
117 ,(17, 'opac', 'login', 'apache', 'authen', oils_i18n_gettext(17, 'OPAC Login (tpac)', 'cuat', 'label'))
118 ,(18, 'staffclient', 'login', 'gateway-v1', 'authen', oils_i18n_gettext(18, 'Staff Client Login', 'cuat', 'label'))
119 ,(19, 'selfcheck', 'login', 'translator-v1','authen', oils_i18n_gettext(19, 'Self-Check Proxy Login', 'cuat', 'label'))
120 ,(20, 'ums', 'login', 'xmlrpc', 'authen', oils_i18n_gettext(20, 'Unique Mgt Login', 'cuat', 'label'))
121 ,(21, 'authproxy', 'login', 'apache', 'authen', oils_i18n_gettext(21, 'Apache Auth Proxy Login', 'cuat', 'label'))
122 ,(22, 'libraryelf', 'login', 'xmlrpc', 'authz', oils_i18n_gettext(22, 'LibraryElf Login', 'cuat', 'label'))
124 ,(23, 'selfcheck', 'verify', 'translator-v1','authz', oils_i18n_gettext(23, 'Self-Check User Verification', 'cuat', 'label'))
125 ,(24, 'ezproxy', 'verify', 'remoteauth', 'authz', oils_i18n_gettext(24, 'EZProxy Verification', 'cuat', 'label'))
129 -- reserve the first 1000 slots
130 SELECT SETVAL('config.usr_activity_type_id_seq'::TEXT, 1000);
132 INSERT INTO config.org_unit_setting_type
133 (name, label, description, grp, datatype)
135 'circ.patron.usr_activity_retrieve.max',
137 'circ.patron.usr_activity_retrieve.max',
138 'Max user activity entries to retrieve (staff client)',
143 'circ.patron.usr_activity_retrieve.max',
144 'Sets the maxinum number of recent user activity entries to retrieve for display in the staff client. 0 means show none, -1 means show all. Default is 1.',
158 DELETE FROM actor.usr_activity;
159 DELETE FROM config.usr_activity_type;
160 DROP TRIGGER remove_transient_usr_activity ON actor.usr_activity;
161 DROP FUNCTION actor.usr_activity_transient_trg();
162 DROP FUNCTION actor.insert_usr_activity(INT, TEXT, TEXT, TEXT);
163 DROP FUNCTION actor.usr_activity_get_type(TEXT, TEXT, TEXT);
164 DROP TABLE actor.usr_activity;
165 DROP TABLE config.usr_activity_type;
166 DROP TYPE config.usr_activity_group;