From d90344ab5b0c5645b306d36faa7b8096893ba5d7 Mon Sep 17 00:00:00 2001 From: Jeff Davis Date: Fri, 15 Sep 2017 16:54:42 -0700 Subject: [PATCH] SQL function for getting random circ chains Signed-off-by: Jeff Davis --- maintenance/random-circ-chains.sql | 71 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 71 insertions(+) create mode 100644 maintenance/random-circ-chains.sql diff --git a/maintenance/random-circ-chains.sql b/maintenance/random-circ-chains.sql new file mode 100644 index 0000000..09c09d4 --- /dev/null +++ b/maintenance/random-circ-chains.sql @@ -0,0 +1,71 @@ +/* + * Copyright (C) 2017 BC Libraries Cooperative + * Author: Jeff Davis + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; either version 2 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + */ + +BEGIN; + +CREATE OR REPLACE FUNCTION sitka.get_random_circ_chains( + circs_needed INT DEFAULT 1 +) RETURNS SETOF action.circulation AS $$ +DECLARE + max_circ_id BIGINT; + circ_count INT; + circs_remaining INT; + circ_id BIGINT; + circ action.circulation%ROWTYPE; +BEGIN + -- What's the largest possible circ ID? + SELECT INTO max_circ_id MAX(id) FROM action.circulation; + + -- Generate random values between 1 and the max possible circ ID. + -- If our desired circ count is 1000, we generate 1000 random values. + CREATE TEMPORARY TABLE random_circ_ids (id BIGINT); + INSERT INTO random_circ_ids (id) + SELECT DISTINCT a.r AS id FROM ( + SELECT floor(random() * max_circ_id)::BIGINT AS r FROM generate_series(1, circs_needed) + ) AS a + INNER JOIN action.circulation b ON a.r = b.id; + + -- Count how many random circ IDs we have with so far. + SELECT INTO circ_count COUNT(*) FROM random_circ_ids; + + -- Random Circ ID generation likely came up with less than our required + -- number of circs, so we keep generating more until we have what we need. + WHILE circ_count < circs_needed LOOP + circs_remaining := circs_needed - circ_count; + INSERT INTO random_circ_ids (id) + SELECT DISTINCT a.r AS id FROM ( + SELECT floor(random() * max_circ_id)::BIGINT AS r FROM generate_series(1, circs_remaining) + ) AS a + INNER JOIN action.circulation b ON a.r = b.id + WHERE a.r NOT IN (SELECT id FROM random_circ_ids) + LIMIT circs_remaining; + SELECT INTO circ_count COUNT(*) FROM random_circ_ids; + END LOOP; + + FOR circ_id IN SELECT id FROM random_circ_ids LOOP + FOR circ IN SELECT * FROM action.circ_chain(circ_id) LOOP + RETURN NEXT circ; + END LOOP; + END LOOP; + + DROP TABLE random_circ_ids; + RETURN; +END; +$$ LANGUAGE PLPGSQL; + +COMMENT ON FUNCTION sitka.get_random_circ_chains(INT) IS 'Returns circs from a specified number of randomly-selected circ chains.'; + +COMMIT; + -- 2.3.6