add bghq ebsco run.sh export script

[sitka/sitka-tools.git] / maintenance / random-circ-chains.sql

/*
 * 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;