[OPEN-ILS-DEV] Functions To Move Or Copy Buckets

[John Merriam]

Hello.  We recently had a request to copy some buckets from one user to 
another.  I did not see any existing functionality to do that in 
Evergreen.  Moving buckets from one user to another is pretty easy to do 
manually but copying them, not so much.  So, I wrote some functions to 
copy and move buckets from one user to another.  I figured I would share 
them in case someone else finds them useful.

They are in the files attached to this message.  One is a support 
function used by the other four.  These functions only cover the bib 
record and copy buckets since those are the only bucket types we are 
using in our database at the moment.  Making new functions to handle the 
other two bucket types (user and call number) using these functions as 
templates should be fairly straightforward.

To load these in to your database so you can make use of them, you would 
need to change all of the *CHANGEME* strings in each file to the name of 
the schema where you want these functions to live.  I put them in one of 
our local custom schemas.

The four copy/move functions all take the same four arguments.  First is 
the source user id. Second is the destination user id.  Third is an 
optional list of bucket ids (surrounded by quotes, separated by commas). 
  Fourth is a boolean (true or false) as to whether the third bucket 
list argument is inclusive or exclusive.

I should mention that I am not an SQL expert so there may be better ways 
to do this.  It is also possible that something like this already exists 
somewhere and we just missed it.

Here are a few usage examples:

To move all copy buckets from userid 10 to user 20:

SELECT YOURSCHEMA.move_container_copy(10, 20, NULL, FALSE);


To copy only one bib record bucket with bucket id 103:

SELECT YOURSCHEMA.copy_container_bib_rec(10, 20, '103', TRUE);


To move all copy buckets EXCEPT bucket ids 204 and 206:

SELECT YOURSCHEMA.move_container_copy(10, 20, '204,206', FALSE);


Any bucket ids specified in the third parameter need to belong to the 
source user or they wont be copied/moved.

-- 

John Merriam
Evergreen Systems Specialist
Bibliomation, Inc.

-------------- next part --------------
BEGIN;

CREATE OR REPLACE FUNCTION *CHANGEME*.copy_container_bib_rec(
    src_usr INT,
    dst_usr INT,
    buckets_str TEXT,
    list_inclusive BOOL)
  RETURNS VOID AS
$$
DECLARE
    bucket_list TEXT[] DEFAULT ARRAY[]::TEXT[];
    src_bucket RECORD;
    dst_bucket RECORD;
    src_bucket_item RECORD;
    dst_bucket_item RECORD;
BEGIN
    IF src_usr = dst_usr THEN
        RETURN;
    END IF;
    IF buckets_str IS NOT NULL THEN
        bucket_list = string_to_array(buckets_str, ',');
    END IF;
    -- Copy buckets
    INSERT INTO container.biblio_record_entry_bucket
      (owner, name, btype, pub, create_time, description, owning_lib)
      SELECT dst_usr, name, btype, pub, create_time, description, owning_lib
        FROM container.biblio_record_entry_bucket
        WHERE owner = src_usr AND
          *CHANGEME*.string_in_array_inc_exc(list_inclusive, id::TEXT, bucket_list) IS TRUE
        ORDER BY id;
    -- Copy bucket items
    -- ORDER BY does not seem to work here?
    FOR src_bucket IN
        SELECT id, name, create_time
          FROM container.biblio_record_entry_bucket
          WHERE owner = src_usr AND
            *CHANGEME*.string_in_array_inc_exc(list_inclusive, id::TEXT, bucket_list) IS TRUE
          ORDER BY id
    LOOP
        SELECT id, name INTO dst_bucket
          FROM container.biblio_record_entry_bucket
          WHERE owner = dst_usr AND
            name = src_bucket.name AND
            create_time = src_bucket.create_time;
        INSERT INTO container.biblio_record_entry_bucket_item
          (bucket, target_biblio_record_entry, pos, create_time)
          SELECT dst_bucket.id, target_biblio_record_entry, pos, create_time
            FROM container.biblio_record_entry_bucket_item
            WHERE bucket = src_bucket.id;
        -- Copy bucket item notes
        -- ORDER BY does not seem to work here?
        FOR src_bucket_item IN
            SELECT id, target_biblio_record_entry, create_time
              FROM container.biblio_record_entry_bucket_item
              WHERE bucket = src_bucket.id
              ORDER BY id
        LOOP
            SELECT id, target_biblio_record_entry INTO dst_bucket_item
              FROM container.biblio_record_entry_bucket_item
              WHERE bucket = dst_bucket.id AND
                target_biblio_record_entry = src_bucket_item.target_biblio_record_entry AND
                create_time = src_bucket_item.create_time;
            INSERT INTO container.biblio_record_entry_bucket_item_note
              (item, note)
              SELECT dst_bucket_item.id, note
                FROM container.biblio_record_entry_bucket_item_note
                WHERE item = src_bucket_item.id;
        END LOOP;
        -- Copy bucket notes
        INSERT INTO container.biblio_record_entry_bucket_note
          (bucket, note)
          SELECT dst_bucket.id, note
            FROM container.biblio_record_entry_bucket_note
            WHERE bucket = src_bucket.id;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

COMMIT;
-------------- next part --------------
BEGIN;

CREATE OR REPLACE FUNCTION *CHANGEME*.copy_container_copy(
    src_usr INT,
    dst_usr INT,
    buckets_str TEXT,
    list_inclusive BOOL)
  RETURNS VOID AS
$$
DECLARE
    bucket_list TEXT[] DEFAULT ARRAY[]::TEXT[];
    src_bucket RECORD;
    dst_bucket RECORD;
    src_bucket_item RECORD;
    dst_bucket_item RECORD;
BEGIN
    IF src_usr = dst_usr THEN
        RETURN;
    END IF;
    IF buckets_str IS NOT NULL THEN
        bucket_list = string_to_array(buckets_str, ',');
    END IF;
    -- Copy buckets
    INSERT INTO container.copy_bucket
      (owner, name, btype, pub, create_time, description, owning_lib)
      SELECT dst_usr, name, btype, pub, create_time, description, owning_lib
        FROM container.copy_bucket
        WHERE owner = src_usr AND
          *CHANGEME*.string_in_array_inc_exc(list_inclusive, id::TEXT, bucket_list) IS TRUE
        ORDER BY id;
    -- Copy bucket items
    -- ORDER BY does not seem to work here?
    FOR src_bucket IN
        SELECT id, name, create_time
          FROM container.copy_bucket
          WHERE owner = src_usr AND
            *CHANGEME*.string_in_array_inc_exc(list_inclusive, id::TEXT, bucket_list) IS TRUE
          ORDER BY id
    LOOP
        SELECT id, name INTO dst_bucket
          FROM container.copy_bucket
          WHERE owner = dst_usr AND
            name = src_bucket.name AND
            create_time = src_bucket.create_time;
        INSERT INTO container.copy_bucket_item
          (bucket, target_copy, pos, create_time)
          SELECT dst_bucket.id, target_copy, pos, create_time
            FROM container.copy_bucket_item
            WHERE bucket = src_bucket.id;
        -- Copy bucket item notes
        -- ORDER BY does not seem to work here?
        FOR src_bucket_item IN
            SELECT id, target_copy, create_time
              FROM container.copy_bucket_item
              WHERE bucket = src_bucket.id
              ORDER BY id
        LOOP
            SELECT id, target_copy INTO dst_bucket_item
              FROM container.copy_bucket_item
              WHERE bucket = dst_bucket.id AND
                target_copy = src_bucket_item.target_copy AND
                create_time = src_bucket_item.create_time;
            INSERT INTO container.copy_bucket_item_note
              (item, note)
              SELECT dst_bucket_item.id, note
                FROM container.copy_bucket_item_note
                WHERE item = src_bucket_item.id;
        END LOOP;
        -- Copy bucket notes
        INSERT INTO container.copy_bucket_note
          (bucket, note)
          SELECT dst_bucket.id, note
            FROM container.copy_bucket_note
            WHERE bucket = src_bucket.id;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

COMMIT;
-------------- next part --------------
BEGIN;

CREATE OR REPLACE FUNCTION *CHANGEME*.move_container_bib_rec(
    src_usr INT,
    dst_usr INT,
    buckets_str TEXT,
    list_inclusive BOOL)
  RETURNS VOID AS
$$
DECLARE
    bucket_list TEXT[] DEFAULT ARRAY[]::TEXT[];
BEGIN
    IF src_usr = dst_usr THEN
        RETURN;
    END IF;
    IF buckets_str IS NOT NULL THEN
        bucket_list = string_to_array(buckets_str, ',');
    END IF;
    -- Move buckets
    UPDATE container.biblio_record_entry_bucket
      SET owner = dst_usr
      WHERE owner = src_usr AND
        *CHANGEME*.string_in_array_inc_exc(list_inclusive, id::TEXT, bucket_list) IS TRUE;
END;
$$
LANGUAGE plpgsql;

COMMIT;
-------------- next part --------------
BEGIN;

CREATE OR REPLACE FUNCTION *CHANGEME*.move_container_copy(
    src_usr INT,
    dst_usr INT,
    buckets_str TEXT,
    list_inclusive BOOL)
  RETURNS VOID AS
$$
DECLARE
    bucket_list TEXT[] DEFAULT ARRAY[]::TEXT[];
BEGIN
    IF src_usr = dst_usr THEN
        RETURN;
    END IF;
    IF buckets_str IS NOT NULL THEN
        bucket_list = string_to_array(buckets_str, ',');
    END IF;
    -- Move buckets
    UPDATE container.copy_bucket
      SET owner = dst_usr
      WHERE owner = src_usr AND
        *CHANGEME*.string_in_array_inc_exc(list_inclusive, id::TEXT, bucket_list) IS TRUE;
END;
$$
LANGUAGE plpgsql;

COMMIT;
-------------- next part --------------
BEGIN;

CREATE OR REPLACE FUNCTION *CHANGEME*.string_in_array_inc_exc(
    list_inclusive BOOL,
    my_string TEXT,
    my_array TEXT[] DEFAULT ARRAY[]::TEXT[])
  RETURNS BOOL AS
$$
BEGIN
    IF my_string IS NULL THEN
        RETURN FALSE;
    END IF;
    -- Inclusive check, return TRUE if string is in array
    IF list_inclusive IS TRUE THEN
        IF my_string = ANY(my_array) THEN
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    -- Exclusive check, return TRUE if string is NOT in array
    ELSE
        IF my_string != ALL(my_array) THEN
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    END IF;
END;
$$
LANGUAGE plpgsql;

COMMIT;