SQL function for getting random circ chains

Signed-off-by: Jeff Davis <jdavis@sitka.bclibraries.ca>

diff --git a/maintenance/random-circ-chains.sql b/maintenance/random-circ-chains.sql
new file mode 100644 (file)
index 0000000..09c09d4
--- /dev/null
+++ b/maintenance/random-circ-chains.sql
@@ -0,0 +1,71 @@
+/*
+ * Copyright (C) 2017 BC Libraries Cooperative
+ * Author: Jeff Davis <jdavis@sitka.bclibraries.ca>
+ *
+ * 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;
+