Open-ILS/src/sql/Pg/upgrade/0681.schema.user-activity.sql

   1 -- Evergreen DB patch 0681.schema.user-activity.sql
   2 --
   3 BEGIN;
   4
   5 -- check whether patch can be applied
   6 SELECT evergreen.upgrade_deps_block_check('0681', :eg_version);
   7
   8 -- SCHEMA --
   9
  10 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
  11
  12 CREATE TABLE config.usr_activity_type (
  13     id          SERIAL                      PRIMARY KEY,
  14     ewho        TEXT,
  15     ewhat       TEXT,
  16     ehow        TEXT,
  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)
  22 );
  23
  24 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
  25     (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
  26
  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()
  32 );
  33
  34 -- remove transient activity entries on insert of new entries
  35 CREATE OR REPLACE FUNCTION actor.usr_activity_transient_trg () RETURNS TRIGGER AS $$
  36 BEGIN
  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
  41             act.usr = NEW.usr;
  42     RETURN NEW;
  43 END;
  44 $$ LANGUAGE PLPGSQL;
  45
  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();
  49
  50 -- given a set of activity criteria, find the most approprate activity type
  51 CREATE OR REPLACE FUNCTION actor.usr_activity_get_type (
  52         ewho TEXT,
  53         ewhat TEXT,
  54         ehow TEXT
  55     ) RETURNS SETOF config.usr_activity_type AS $$
  56 SELECT * FROM config.usr_activity_type
  57     WHERE
  58         enabled AND
  59         (ewho  IS NULL OR ewho  = $1) AND
  60         (ewhat IS NULL OR ewhat = $2) AND
  61         (ehow  IS NULL OR ehow  = $3)
  62     ORDER BY
  63         -- BOOL comparisons sort false to true
  64         COALESCE(ewho, '')  != COALESCE($1, ''),
  65         COALESCE(ewhat,'')  != COALESCE($2, ''),
  66         COALESCE(ehow, '')  != COALESCE($3, '')
  67     LIMIT 1;
  68 $$ LANGUAGE SQL;
  69
  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 (
  73         usr INT,
  74         ewho TEXT,
  75         ewhat TEXT,
  76         ehow TEXT
  77     ) RETURNS SETOF actor.usr_activity AS $$
  78 DECLARE
  79     new_row actor.usr_activity%ROWTYPE;
  80 BEGIN
  81     SELECT id INTO new_row.etype FROM actor.usr_activity_get_type(ewho, ewhat, ehow);
  82     IF FOUND THEN
  83         new_row.usr := usr;
  84         INSERT INTO actor.usr_activity (usr, etype)
  85             VALUES (usr, new_row.etype)
  86             RETURNING * INTO new_row;
  87         RETURN NEXT new_row;
  88     END IF;
  89 END;
  90 $$ LANGUAGE plpgsql;
  91
  92 -- SEED DATA --
  93
  94 INSERT INTO config.usr_activity_type (id, ewho, ewhat, ehow, egroup, label) VALUES
  95
  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'))
 106
 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'))
 114
 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'))
 123
 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'))
 126     -- ...
 127     ;
 128
 129 -- reserve the first 1000 slots
 130 SELECT SETVAL('config.usr_activity_type_id_seq'::TEXT, 1000);
 131
 132 INSERT INTO config.org_unit_setting_type
 133     (name, label, description, grp, datatype)
 134     VALUES (
 135         'circ.patron.usr_activity_retrieve.max',
 136          oils_i18n_gettext(
 137             'circ.patron.usr_activity_retrieve.max',
 138             'Max user activity entries to retrieve (staff client)',
 139             'coust',
 140             'label'
 141         ),
 142         oils_i18n_gettext(
 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.',
 145             'coust',
 146             'description'
 147         ),
 148         'gui',
 149         'integer'
 150     );
 151
 152
 153 COMMIT;
 154
 155 /*
 156 -- UNDO SQL --
 157 BEGIN;
 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;
 167 COMMIT;
 168 */