install-eg.sh: fix error in tarball download
[sitka/sitka-tools.git] / maintenance / random-circ-chains.sql
CommitLineData
d90344ab
JD
1/*
2 * Copyright (C) 2017 BC Libraries Cooperative
3 * Author: Jeff Davis <jdavis@sitka.bclibraries.ca>
4 *
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 */
15
16BEGIN;
17
18CREATE OR REPLACE FUNCTION sitka.get_random_circ_chains(
19 circs_needed INT DEFAULT 1
20) RETURNS SETOF action.circulation AS $$
21DECLARE
22 max_circ_id BIGINT;
23 circ_count INT;
24 circs_remaining INT;
25 circ_id BIGINT;
26 circ action.circulation%ROWTYPE;
27BEGIN
28 -- What's the largest possible circ ID?
29 SELECT INTO max_circ_id MAX(id) FROM action.circulation;
30
31 -- Generate random values between 1 and the max possible circ ID.
32 -- If our desired circ count is 1000, we generate 1000 random values.
33 CREATE TEMPORARY TABLE random_circ_ids (id BIGINT);
34 INSERT INTO random_circ_ids (id)
35 SELECT DISTINCT a.r AS id FROM (
36 SELECT floor(random() * max_circ_id)::BIGINT AS r FROM generate_series(1, circs_needed)
37 ) AS a
38 INNER JOIN action.circulation b ON a.r = b.id;
39
40 -- Count how many random circ IDs we have with so far.
41 SELECT INTO circ_count COUNT(*) FROM random_circ_ids;
42
43 -- Random Circ ID generation likely came up with less than our required
44 -- number of circs, so we keep generating more until we have what we need.
45 WHILE circ_count < circs_needed LOOP
46 circs_remaining := circs_needed - circ_count;
47 INSERT INTO random_circ_ids (id)
48 SELECT DISTINCT a.r AS id FROM (
49 SELECT floor(random() * max_circ_id)::BIGINT AS r FROM generate_series(1, circs_remaining)
50 ) AS a
51 INNER JOIN action.circulation b ON a.r = b.id
52 WHERE a.r NOT IN (SELECT id FROM random_circ_ids)
53 LIMIT circs_remaining;
54 SELECT INTO circ_count COUNT(*) FROM random_circ_ids;
55 END LOOP;
56
57 FOR circ_id IN SELECT id FROM random_circ_ids LOOP
58 FOR circ IN SELECT * FROM action.circ_chain(circ_id) LOOP
59 RETURN NEXT circ;
60 END LOOP;
61 END LOOP;
62
63 DROP TABLE random_circ_ids;
64 RETURN;
65END;
66$$ LANGUAGE PLPGSQL;
67
68COMMENT ON FUNCTION sitka.get_random_circ_chains(INT) IS 'Returns circs from a specified number of randomly-selected circ chains.';
69
70COMMIT;
71