Open-ILS/src/sql/Pg/upgrade/0677.schema.circ_limits.sql

   1 -- Evergreen DB patch 0677.schema.circ_limits.sql
   2 --
   3 -- FIXME: insert description of change, if needed
   4 --
   5 BEGIN;
   6
   7
   8 -- check whether patch can be applied
   9 SELECT evergreen.upgrade_deps_block_check('0677', :eg_version);
  10
  11 -- FIXME: add/check SQL statements to perform the upgrade
  12 -- Limit groups for circ counting
  13 CREATE TABLE config.circ_limit_group (
  14     id          SERIAL  PRIMARY KEY,
  15     name        TEXT    UNIQUE NOT NULL,
  16     description TEXT
  17 );
  18
  19 -- Limit sets
  20 CREATE TABLE config.circ_limit_set (
  21     id          SERIAL  PRIMARY KEY,
  22     name        TEXT    UNIQUE NOT NULL,
  23     owning_lib  INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  24     items_out   INT     NOT NULL, -- Total current active circulations must be less than this. 0 means skip counting (always pass)
  25     depth       INT     NOT NULL DEFAULT 0, -- Depth count starts at
  26     global      BOOL    NOT NULL DEFAULT FALSE, -- If enabled, include everything below depth, otherwise ancestors/descendants only
  27     description TEXT
  28 );
  29
  30 -- Linkage between matchpoints and limit sets
  31 CREATE TABLE config.circ_matrix_limit_set_map (
  32     id          SERIAL  PRIMARY KEY,
  33     matchpoint  INT     NOT NULL REFERENCES config.circ_matrix_matchpoint (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  34     limit_set   INT     NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  35     fallthrough BOOL    NOT NULL DEFAULT FALSE, -- If true fallthrough will grab this rule as it goes along
  36     active      BOOL    NOT NULL DEFAULT TRUE,
  37     CONSTRAINT circ_limit_set_once_per_matchpoint UNIQUE (matchpoint, limit_set)
  38 );
  39
  40 -- Linkage between limit sets and circ mods
  41 CREATE TABLE config.circ_limit_set_circ_mod_map (
  42     id          SERIAL  PRIMARY KEY,
  43     limit_set   INT     NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  44     circ_mod    TEXT    NOT NULL REFERENCES config.circ_modifier (code) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  45     CONSTRAINT cm_once_per_set UNIQUE (limit_set, circ_mod)
  46 );
  47
  48 -- Linkage between limit sets and limit groups
  49 CREATE TABLE config.circ_limit_set_group_map (
  50     id          SERIAL  PRIMARY KEY,
  51     limit_set    INT     NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  52     limit_group INT     NOT NULL REFERENCES config.circ_limit_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  53     check_only  BOOL    NOT NULL DEFAULT FALSE, -- If true, don't accumulate this limit_group for storing with the circulation
  54     CONSTRAINT clg_once_per_set UNIQUE (limit_set, limit_group)
  55 );
  56
  57 -- Linkage between limit groups and circulations
  58 CREATE TABLE action.circulation_limit_group_map (
  59     circ        BIGINT      NOT NULL REFERENCES action.circulation (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  60     limit_group INT         NOT NULL REFERENCES config.circ_limit_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  61     PRIMARY KEY (circ, limit_group)
  62 );
  63
  64 -- Function for populating the circ/limit group mappings
  65 CREATE OR REPLACE FUNCTION action.link_circ_limit_groups ( BIGINT, INT[] ) RETURNS VOID AS $func$
  66     INSERT INTO action.circulation_limit_group_map(circ, limit_group) SELECT $1, id FROM config.circ_limit_group WHERE id IN (SELECT * FROM UNNEST($2));
  67 $func$ LANGUAGE SQL;
  68
  69 DROP TYPE IF EXISTS action.circ_matrix_test_result CASCADE;
  70 CREATE TYPE action.circ_matrix_test_result AS ( success BOOL, fail_part TEXT, buildrows INT[], matchpoint INT, circulate BOOL, duration_rule INT, recurring_fine_rule INT, max_fine_rule INT, hard_due_date INT, renewals INT, grace_period INTERVAL, limit_groups INT[] );
  71
  72 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
  73 DECLARE
  74     user_object             actor.usr%ROWTYPE;
  75     standing_penalty        config.standing_penalty%ROWTYPE;
  76     item_object             asset.copy%ROWTYPE;
  77     item_status_object      config.copy_status%ROWTYPE;
  78     item_location_object    asset.copy_location%ROWTYPE;
  79     result                  action.circ_matrix_test_result;
  80     circ_test               action.found_circ_matrix_matchpoint;
  81     circ_matchpoint         config.circ_matrix_matchpoint%ROWTYPE;
  82     circ_limit_set          config.circ_limit_set%ROWTYPE;
  83     hold_ratio              action.hold_stats%ROWTYPE;
  84     penalty_type            TEXT;
  85     items_out               INT;
  86     context_org_list        INT[];
  87     done                    BOOL := FALSE;
  88 BEGIN
  89     -- Assume success unless we hit a failure condition
  90     result.success := TRUE;
  91
  92     -- Need user info to look up matchpoints
  93     SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
  94
  95     -- (Insta)Fail if we couldn't find the user
  96     IF user_object.id IS NULL THEN
  97         result.fail_part := 'no_user';
  98         result.success := FALSE;
  99         done := TRUE;
 100         RETURN NEXT result;
 101         RETURN;
 102     END IF;
 103
 104     -- Need item info to look up matchpoints
 105     SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
 106
 107     -- (Insta)Fail if we couldn't find the item
 108     IF item_object.id IS NULL THEN
 109         result.fail_part := 'no_item';
 110         result.success := FALSE;
 111         done := TRUE;
 112         RETURN NEXT result;
 113         RETURN;
 114     END IF;
 115
 116     SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
 117
 118     circ_matchpoint             := circ_test.matchpoint;
 119     result.matchpoint           := circ_matchpoint.id;
 120     result.circulate            := circ_matchpoint.circulate;
 121     result.duration_rule        := circ_matchpoint.duration_rule;
 122     result.recurring_fine_rule  := circ_matchpoint.recurring_fine_rule;
 123     result.max_fine_rule        := circ_matchpoint.max_fine_rule;
 124     result.hard_due_date        := circ_matchpoint.hard_due_date;
 125     result.renewals             := circ_matchpoint.renewals;
 126     result.grace_period         := circ_matchpoint.grace_period;
 127     result.buildrows            := circ_test.buildrows;
 128
 129     -- (Insta)Fail if we couldn't find a matchpoint
 130     IF circ_test.success = false THEN
 131         result.fail_part := 'no_matchpoint';
 132         result.success := FALSE;
 133         done := TRUE;
 134         RETURN NEXT result;
 135         RETURN;
 136     END IF;
 137
 138     -- All failures before this point are non-recoverable
 139     -- Below this point are possibly overridable failures
 140
 141     -- Fail if the user is barred
 142     IF user_object.barred IS TRUE THEN
 143         result.fail_part := 'actor.usr.barred';
 144         result.success := FALSE;
 145         done := TRUE;
 146         RETURN NEXT result;
 147     END IF;
 148
 149     -- Fail if the item can't circulate
 150     IF item_object.circulate IS FALSE THEN
 151         result.fail_part := 'asset.copy.circulate';
 152         result.success := FALSE;
 153         done := TRUE;
 154         RETURN NEXT result;
 155     END IF;
 156
 157     -- Fail if the item isn't in a circulateable status on a non-renewal
 158     IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
 159         result.fail_part := 'asset.copy.status';
 160         result.success := FALSE;
 161         done := TRUE;
 162         RETURN NEXT result;
 163     -- Alternately, fail if the item isn't checked out on a renewal
 164     ELSIF renewal AND item_object.status <> 1 THEN
 165         result.fail_part := 'asset.copy.status';
 166         result.success := FALSE;
 167         done := TRUE;
 168         RETURN NEXT result;
 169     END IF;
 170
 171     -- Fail if the item can't circulate because of the shelving location
 172     SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
 173     IF item_location_object.circulate IS FALSE THEN
 174         result.fail_part := 'asset.copy_location.circulate';
 175         result.success := FALSE;
 176         done := TRUE;
 177         RETURN NEXT result;
 178     END IF;
 179
 180     -- Use Circ OU for penalties and such
 181     SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
 182
 183     IF renewal THEN
 184         penalty_type = '%RENEW%';
 185     ELSE
 186         penalty_type = '%CIRC%';
 187     END IF;
 188
 189     FOR standing_penalty IN
 190         SELECT  DISTINCT csp.*
 191           FROM  actor.usr_standing_penalty usp
 192                 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
 193           WHERE usr = match_user
 194                 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
 195                 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
 196                 AND csp.block_list LIKE penalty_type LOOP
 197
 198         result.fail_part := standing_penalty.name;
 199         result.success := FALSE;
 200         done := TRUE;
 201         RETURN NEXT result;
 202     END LOOP;
 203
 204     -- Fail if the test is set to hard non-circulating
 205     IF circ_matchpoint.circulate IS FALSE THEN
 206         result.fail_part := 'config.circ_matrix_test.circulate';
 207         result.success := FALSE;
 208         done := TRUE;
 209         RETURN NEXT result;
 210     END IF;
 211
 212     -- Fail if the total copy-hold ratio is too low
 213     IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
 214         SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 215         IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
 216             result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
 217             result.success := FALSE;
 218             done := TRUE;
 219             RETURN NEXT result;
 220         END IF;
 221     END IF;
 222
 223     -- Fail if the available copy-hold ratio is too low
 224     IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
 225         IF hold_ratio.hold_count IS NULL THEN
 226             SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
 227         END IF;
 228         IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
 229             result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
 230             result.success := FALSE;
 231             done := TRUE;
 232             RETURN NEXT result;
 233         END IF;
 234     END IF;
 235
 236     -- Fail if the user has too many items out by defined limit sets
 237     FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
 238       JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
 239       WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
 240         ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
 241         ) LOOP
 242             IF circ_limit_set.items_out > 0 AND NOT renewal THEN
 243                 SELECT INTO context_org_list ARRAY_AGG(aou.id)
 244                   FROM actor.org_unit_full_path( circ_ou ) aou
 245                     JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
 246                   WHERE aout.depth >= circ_limit_set.depth;
 247                 IF circ_limit_set.global THEN
 248                     WITH RECURSIVE descendant_depth AS (
 249                         SELECT  ou.id,
 250                             ou.parent_ou
 251                         FROM  actor.org_unit ou
 252                         WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
 253                             UNION
 254                         SELECT  ou.id,
 255                             ou.parent_ou
 256                         FROM  actor.org_unit ou
 257                             JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
 258                     ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
 259                 END IF;
 260                 SELECT INTO items_out COUNT(DISTINCT circ.id)
 261                   FROM action.circulation circ
 262                     JOIN asset.copy copy ON (copy.id = circ.target_copy)
 263                     LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
 264                   WHERE circ.usr = match_user
 265                     AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
 266                     AND circ.checkin_time IS NULL
 267                     AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
 268                     AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
 269                         OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
 270                     );
 271                 IF items_out >= circ_limit_set.items_out THEN
 272                     result.fail_part := 'config.circ_matrix_circ_mod_test';
 273                     result.success := FALSE;
 274                     done := TRUE;
 275                     RETURN NEXT result;
 276                 END IF;
 277             END IF;
 278             SELECT INTO result.limit_groups result.limit_groups || ARRAY_AGG(limit_group) FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id AND NOT check_only;
 279     END LOOP;
 280
 281     -- If we passed everything, return the successful matchpoint
 282     IF NOT done THEN
 283         RETURN NEXT result;
 284     END IF;
 285
 286     RETURN;
 287 END;
 288 $func$ LANGUAGE plpgsql;
 289
 290 -- We need to re-create these, as they got dropped with the type above.
 291 CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
 292     SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );
 293 $func$ LANGUAGE SQL;
 294
 295 CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$
 296     SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );
 297 $func$ LANGUAGE SQL;
 298
 299 -- Temp function for migrating circ mod limits.
 300 CREATE OR REPLACE FUNCTION evergreen.temp_migrate_circ_mod_limits() RETURNS VOID AS $func$
 301 DECLARE
 302     circ_mod_group config.circ_matrix_circ_mod_test%ROWTYPE;
 303     current_set INT;
 304     circ_mod_count INT;
 305 BEGIN
 306     FOR circ_mod_group IN SELECT * FROM config.circ_matrix_circ_mod_test LOOP
 307         INSERT INTO config.circ_limit_set(name, owning_lib, items_out, depth, global, description)
 308             SELECT org_unit || ' : Matchpoint ' || circ_mod_group.matchpoint || ' : Circ Mod Test ' || circ_mod_group.id, org_unit, circ_mod_group.items_out, 0, false, 'Migrated from Circ Mod Test System'
 309                 FROM config.circ_matrix_matchpoint WHERE id = circ_mod_group.matchpoint
 310             RETURNING id INTO current_set;
 311         INSERT INTO config.circ_matrix_limit_set_map(matchpoint, limit_set, fallthrough, active) VALUES (circ_mod_group.matchpoint, current_set, false, true);
 312         INSERT INTO config.circ_limit_set_circ_mod_map(limit_set, circ_mod)
 313             SELECT current_set, circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = circ_mod_group.id;
 314         SELECT INTO circ_mod_count count(id) FROM config.circ_limit_set_circ_mod_map WHERE limit_set = current_set;
 315         RAISE NOTICE 'Created limit set with id % and % circ modifiers attached to matchpoint %', current_set, circ_mod_count, circ_mod_group.matchpoint;
 316     END LOOP;
 317 END;
 318 $func$ LANGUAGE plpgsql;
 319
 320 -- Run the temp function
 321 SELECT * FROM evergreen.temp_migrate_circ_mod_limits();
 322
 323 -- Drop the temp function
 324 DROP FUNCTION evergreen.temp_migrate_circ_mod_limits();
 325
 326 --Drop the old tables
 327 --Not sure we want to do this. Keeping them may help "something went wrong" correction.
 328 --DROP TABLE IF EXISTS config.circ_matrix_circ_mod_test_map, config.circ_matrix_circ_mod_test;
 329
 330
 331 COMMIT;